Calgarypuck Forums - The Unofficial Calgary Flames Fan Community

Go Back   Calgarypuck Forums - The Unofficial Calgary Flames Fan Community > Main Forums > The Off Topic Forum > Tech Talk
Ivrnet

Notices

Reply
 
Thread Tools
Old 08-04-2015, 10:44 AM   #1
RW99
First Line Centre
 
RW99's Avatar
 
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 202 203 208 216 217 219 221 222 225 313 317 Heritage Classic G
Exp:
Question Excel questions thread

I see through search we have plenty of individual question threads and bumping one of them didn't seem like the best option. So I've created this general question thread for Excel noobs like myself.

My issue is date formatting. I have a .xls file at work, on Excel 2010, Windows 7. When I bring it home to my Excel 2013, Windows 7, I can not get date formatting to work. On the spreadsheet dates are entered as 08/04/2015. When I enter new dates at home in Excel 2013, it keeps changing to 8/4/2015.

I'm guessing the issue is in my regional settings for Windows 7? I've gone through all of those, making sure I selected mm/dd/yyyy, changing from US to Canada, etc. But for the life of me I still cant get Excel to recognize those zeros. Any ideas? Searching online has been a headache as well, just cant find my specific issue anywhere.
RW99 is offline   Reply With Quote
Old 08-04-2015, 11:31 AM   #2
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

When you go to format cells, how do you see the date? If I have mine set to "English(Canada)" I get 14/03/2012. Changing to English(United States) I get I get 3/14/2012. Now, this will only function on those cells in that sheet. Excel uses system defaults, which you should be able to change in the language control panel applet.
Fuzz is offline   Reply With Quote
The Following User Says Thank You to Fuzz For This Useful Post:
Old 08-04-2015, 01:43 PM   #3
Old Yeller
First Line Centre
 
Join Date: Mar 2004
Exp:
Default

What happens if you just insert a column and use:

=TEXT(value,"dd/mm/yyyy")

Yeah it converts it to a string but does it preserve the formatting?
Old Yeller is offline   Reply With Quote
Old 08-04-2015, 02:37 PM   #4
RW99
First Line Centre
 
RW99's Avatar
 
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 202 203 208 216 217 219 221 222 225 313 317 Heritage Classic G
Exp:
Default

I think I was able to solve it by selecting the column and formatting it with the date format. So I guess I'm overriding the default format of a particular PC? So no matter what PC I open it with, it will format correctly each time.
RW99 is offline   Reply With Quote
Old 08-04-2015, 02:40 PM   #5
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

Ya, and if you go to the control panel I mentioned, you should be able to set the default format for both PC's to be the same.
Fuzz is offline   Reply With Quote
The Following User Says Thank You to Fuzz For This Useful Post:
Old 06-01-2016, 04:24 PM   #6
RW99
First Line Centre
 
RW99's Avatar
 
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 202 203 208 216 217 219 221 222 225 313 317 Heritage Classic G
Exp:
Default

So another Excel question pops up on CP! Any help would be greatly appreciated. I have this formula:

=COUNTIFS(RAW_DATA!D:D,{"Yellow","Brown","Green"}, RAW_DATA!G:G,"<15000",RAW_DATA!P:P,{"Apple","Pear" ,"Cherry"})

This formula creates no errors so it appears to be working. I want to count the rows that have yellow, brown or green in column D, below 15000 in column G (including zeros) and is also an apple, pear or cherry in column P. However the calculation is not accurate. This formula will generate say a count of 415. But if I go into the sheet and use Filters and manually count, I get 625. Any ideas?
RW99 is offline   Reply With Quote
Old 06-02-2016, 08:07 AM   #7
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

did you press ctrl-shift-enter after modifying the formula? It is an array, so you have to do that.
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is offline   Reply With Quote
The Following User Says Thank You to Fuzz For This Useful Post:
Old 06-02-2016, 09:24 AM   #8
RW99
First Line Centre
 
RW99's Avatar
 
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 202 203 208 216 217 219 221 222 225 313 317 Heritage Classic G
Exp:
Default

Yeah I dont have much experience with arrays. I did that and it added the {} brackets to the entire formula, no change. I may have to spend some time learning them
RW99 is offline   Reply With Quote
Old 06-02-2016, 10:01 AM   #9
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

"Apple","Pear" ,"Cherry"
There is a space after "Pear" try removing that.
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is offline   Reply With Quote
Old 07-19-2016, 01:59 PM   #10
ResAlien
Lifetime In Suspension
 
ResAlien's Avatar
 
Join Date: Dec 2007
Exp:
Default

Any idea why inputting a formula would only work via that stupid formula button instead of just manually typing it in? One damn company's report I can no longer do Vlookups manually, dives me nuts. As soon as I try to select the range it tells me there's too few arguments. They must have changed some format but damned if I can figure it out. Any ideas from my crap explanation?
ResAlien is offline   Reply With Quote
Old 07-19-2016, 02:04 PM   #11
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

is it an array? You need to press "shift-enter" to activate it.
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is offline   Reply With Quote
Old 07-19-2016, 02:15 PM   #12
ResAlien
Lifetime In Suspension
 
ResAlien's Avatar
 
Join Date: Dec 2007
Exp:
Default

...shift and enter you say? What sorcery is this. Goddammit now I feel stupid.

No, that didn't fix it I don't think. Odd. I don't know what the hell

So like this is what happens. I do =vlookup(A2,alt/tab to my other sheet, A2 again and hold down shift, go over two rows to what I want, and as soon as I I go to hit the down arrow and ctrl along with shift it reacts like I've hit enter to end my formula.

That explanation was probably terrible.

Last edited by ResAlien; 07-19-2016 at 02:21 PM.
ResAlien is offline   Reply With Quote
Old 07-19-2016, 02:31 PM   #13
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

Hrmm, weird. Have you tried re-booting? The only thing I would suspect is the sheet you are doing a vlookup on had the the row outside of what is expected, so if you press shft-ctrl-down on it it doesn't stop at your last data entry, it goes way below. You could test doing that outside a formula, just to see. Of course, you should be able to manually enter the formula but who wants to type all that, eh?
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is offline   Reply With Quote
Old 07-19-2016, 02:33 PM   #14
ResAlien
Lifetime In Suspension
 
ResAlien's Avatar
 
Join Date: Dec 2007
Exp:
Default

Bah there is no god. This adds like 80 second to my workday for one function. Unreasonabl. It's only when I hit ctrl that it gdoes stupid. Guess I'll send this off to Robert Stack.

Last edited by ResAlien; 07-19-2016 at 02:36 PM.
ResAlien is offline   Reply With Quote
Old 07-19-2016, 02:40 PM   #15
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

You could try calling Microsoft tech support. *snickers*
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is offline   Reply With Quote
The Following User Says Thank You to Fuzz For This Useful Post:
Old 07-19-2016, 02:43 PM   #16
puckedoff
Powerplay Quarterback
 
puckedoff's Avatar
 
Join Date: Apr 2013
Exp:
Default

Quote:
Originally Posted by ResAlien View Post
Bah there is no god. This adds like 80 second to my workday for one function. Unreasonabl. It's only when I hit ctrl that it gdoes stupid. Guess I'll send this off to Robert Stack.
My guess is that you are tabbing to a spreadsheet that is opened in a different 'instance' of Excel. (sort of like, you have opened excel twice and the sheets are in each separate Excel). If you close the one book, and then open it from the same excel (file -> open) when you are staring at your first book then your formula clicking should work.
puckedoff is offline   Reply With Quote
Old 07-19-2016, 02:49 PM   #17
polak
In the Sin Bin
 
Join Date: Aug 2012
Exp:
Default

Quote:
Originally Posted by ResAlien View Post
...shift and enter you say? What sorcery is this. Goddammit now I feel stupid.

No, that didn't fix it I don't think. Odd. I don't know what the hell

So like this is what happens. I do =vlookup(A2,alt/tab to my other sheet, A2 again and hold down shift, go over two rows to what I want, and as soon as I I go to hit the down arrow and ctrl along with shift it reacts like I've hit enter to end my formula.

That explanation was probably terrible.
Are you sure it's catching you change to the other sheet? So does it show =vlookup(A2,Sheet2!A:X,etc...)?
polak is offline   Reply With Quote
Old 07-19-2016, 02:53 PM   #18
ResAlien
Lifetime In Suspension
 
ResAlien's Avatar
 
Join Date: Dec 2007
Exp:
Default

^tried that, didn't resolve it. Something about the worksheet coming from the other company is making the ctrl button do crazy things. Guess I'll just use page down instead. Goddam odd though, it's only this one report from one person and she swears she's changed nothing. She's obviously devious and up to something sinister. Thanks for trying to help, guys. I do appreciate it.

Yes Polak, I'm sure. Every other way of doing it works just fine, just the ctrl button acts like enter. Hence my confusion

I KNOW IT'S A WOLF, OK? IT COULDN'T POSSIBLY BE A DOG!

Last edited by ResAlien; 07-19-2016 at 02:58 PM.
ResAlien is offline   Reply With Quote
Old 07-20-2016, 02:14 PM   #19
mrkajz44
First Line Centre
 
mrkajz44's Avatar
 
Join Date: Oct 2010
Location: Deep South
Exp:
Default

Could it be that the VLOOKUP is looking for the true/false argument at the end when you are not using the formula wizard? I think if you use the wizard then it defaults to false, but maybe it's not smart enough to do that when you just type it in without the wizard?

Likely more possible if that is an older version of a spreadsheet being used in an updated version of excel.
__________________
Much like a sports ticker, you may feel obligated to read this
mrkajz44 is offline   Reply With Quote
Old 11-23-2016, 05:02 PM   #20
RW99
First Line Centre
 
RW99's Avatar
 
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 202 203 208 216 217 219 221 222 225 313 317 Heritage Classic G
Exp:
Default

So I'm back with another question for the CP braintrust, any help would be appreciated. I have this spreadsheet:



The green section is fairly straight forward, I have to distribute files every day to a group of six staff. Today I am distributing 1000 files (D2). Of these six staff, Shannon and Greg are slackers, so they only get 10.6% of the files, everyone else is even at 19.7% (A5:A10).

The yellow section is where it gets tricky. Some staff are here a full day and some are half days and some call in sick to play Battlefield 1 (come on Terry...). So the question is what would my formula look like in the red section (H5:H10) so that regardless of who shows up to work all 1000 files get assigned?

I think I'm asking for a lot here, I'm assuming the formula might be fairly long
RW99 is offline   Reply With Quote
Reply

Tags
excel

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 12:20 AM.

Calgary Flames
2016-17




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2016