If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
WEEKDAY()
Regarding WEEKDAY(), I read that problems can occur if dates are entered as text.
Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong result (7). This is because general format is same as text. If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5). Okay, so far. What I don't understand is the following. I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also get the correct result (5). The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008 *directly* to the formula. It amazes me that referencing A1 in WEEKDAY() gives me the correct answer whereas keying it in as part of the formula won't work. Comments welcome. Epinn |
#2
|
|||
|
|||
WEEKDAY()
But the difference is that when you key 21/4/2008 into cell A1, Excel
recognises it as a date and converts it to an underlying value of 39559, which it presents/formats as that date. When you enter it into a function, the function treats it as its argument, and says that it is invalid as it expects a number (the true underlying value). 21/4/2008 is not a number here, so it errors. If you want to enter the date into the WEEKDAY function, you have to force it into a number, either using another function such as you did with DATE, or coerce it directly, like =WEEKDAY(--"21/4/2008") or my preferred format of =WEEKDAY(--"2008-04-21") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Regarding WEEKDAY(), I read that problems can occur if dates are entered as text. Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong result (7). This is because general format is same as text. If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5). Okay, so far. What I don't understand is the following. I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also get the correct result (5). The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008 *directly* to the formula. It amazes me that referencing A1 in WEEKDAY() gives me the correct answer whereas keying it in as part of the formula won't work. Comments welcome. Epinn |
#3
|
|||
|
|||
WEEKDAY()
Hi Bob,
Can you also explain another one along similar lines. If I put todays date 09/09/2006 in A1. Then in A2 I put =MONTH(A1) and I get 9 which is good. I then format A2 as mmmm and I get January. What gives?? Confused Martin |
#4
|
|||
|
|||
WEEKDAY()
Again it is because the underlying values of dates are just numbers. As I
said to Epinn, the value stored in a date of 21/4/2008 is 39559. Now when you do a =MONTH(A1) in A2, you are not really returning a date, but a simple month number (9 in the case quoted). But, because it is a number, if you do date type things on it, Excel will not complain, it will just work on whatever date that number resolves to. As a date is stored as the number of days since 1st January 1900, the value of 9 will be treated as 9th Jan 1900, so if you format it as mmmm, you get January. Format it as dd/mm/yyyy, and see what I mean. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MartinW" wrote in message ... Hi Bob, Can you also explain another one along similar lines. If I put todays date 09/09/2006 in A1. Then in A2 I put =MONTH(A1) and I get 9 which is good. I then format A2 as mmmm and I get January. What gives?? Confused Martin |
#5
|
|||
|
|||
WEEKDAY()
Bob,
Glad I still caught you this late. Thank you for enlightening me. 21/4/2008 is not a number here, so it errors. If it gives me an error, then I know. The problem is it returns "7" instead of "5" in my other example. That's very misleading and dangerous. =WEEKDAY(--"21/4/2008") gives #VALUE! =WEEKDAYS(--"4/21/2008") gives 2. So, I understand why you prefer =WEEKDAY(--"2008-04-21"). But I prefer "/" to "-" and I tested it. I am glad that "/" works too. My preference will be =WEEKDAY(--"2008/4/21"). I assume "--" above is the same as "--" in SUMPRODUCT(). Please confirm. Can't find double negating in Help. I find date functions may be as confusing as SUMPRODUCT(). Will see. Epinn "Bob Phillips" wrote in message ... But the difference is that when you key 21/4/2008 into cell A1, Excel recognises it as a date and converts it to an underlying value of 39559, which it presents/formats as that date. When you enter it into a function, the function treats it as its argument, and says that it is invalid as it expects a number (the true underlying value). 21/4/2008 is not a number here, so it errors. If you want to enter the date into the WEEKDAY function, you have to force it into a number, either using another function such as you did with DATE, or coerce it directly, like =WEEKDAY(--"21/4/2008") or my preferred format of =WEEKDAY(--"2008-04-21") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Regarding WEEKDAY(), I read that problems can occur if dates are entered as text. Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong result (7). This is because general format is same as text. If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5). Okay, so far. What I don't understand is the following. I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also get the correct result (5). The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008 *directly* to the formula. It amazes me that referencing A1 in WEEKDAY() gives me the correct answer whereas keying it in as part of the formula won't work. Comments welcome. Epinn |
#6
|
|||
|
|||
WEEKDAY()
Thanks Bob, of course it is I should have seen that...duh!
So to get a return of September I can see a way of doing it by hiding the month cell and a Vlookup table somewhere out of the way and using a Vlookup in A2. Is there a simpler method? Thanks again Martin |
#7
|
|||
|
|||
WEEKDAY()
Because weekday(21/4/2008) does give an error
as it treats 21/4/2008 as 21/4 divided by 2008 - nearly 0 ie 0/1/1900 in date terms Odd but thats what's happening Steve On Sat, 09 Sep 2006 01:52:22 +0100, Epinn wrote: Bob, Glad I still caught you this late. Thank you for enlightening me. 21/4/2008 is not a number here, so it errors. If it gives me an error, then I know. The problem is it returns "7" instead of "5" in my other example. That's very misleading and dangerous. =WEEKDAY(--"21/4/2008") gives #VALUE! =WEEKDAYS(--"4/21/2008") gives 2. So, I understand why you prefer =WEEKDAY(--"2008-04-21"). But I prefer "/" to "-" and I tested it. I am glad that "/" works too.. My preference will be =WEEKDAY(--"2008/4/21"). I assume "--" above is the same as "--" in SUMPRODUCT(). Please confirm. Can't find double negating in Help. I find date functions may be as confusing as SUMPRODUCT(). Will see. Epinn "Bob Phillips" wrote in message ... But the difference is that when you key 21/4/2008 into cell A1, Excel recognises it as a date and converts it to an underlying value of 39559, which it presents/formats as that date. When you enter it into a function, the function treats it as its argument, and says that it is invalid as it expects a number (the true underlying value). 21/4/2008 is not a number here, so it errors. If you want to enter the date into the WEEKDAY function, you have to force it into a number, either using another function such as you did with DATE, or coerce it directly, like =WEEKDAY(--"21/4/2008") or my preferred format of =WEEKDAY(--"2008-04-21") -- Steve (3) |
#8
|
|||
|
|||
WEEKDAY()
When you enter it into a function, the function treats it as its argument,
and says that it is invalid as it expects a number (the true underlying value). 21/4/2008 is not a number here, so it errors. Actually, the formula does not "error" but calculates properly. The argument: 2/14/2008 is the equivalent of: 2 divided by 14 divided by 2008 which equals: 0.0000711439954467843 Since WEEKDAY only works with integers Excel truncates the value to 0. Weekday( 0 ) is actually 12/31/1899 which is a Saturday or weekday 7 when the return_type used is 1 or omitted. This is another Excel nuance. The date serial system doesn't start until day 1 which is 1/1/1900 yet you can calculate a date to be 1/0/1900. Excel treats the 0th day of the month as the last day of the previous month. That's how we end up with 7 as the result of the Weekday function. Biff "Bob Phillips" wrote in message ... But the difference is that when you key 21/4/2008 into cell A1, Excel recognises it as a date and converts it to an underlying value of 39559, which it presents/formats as that date. When you enter it into a function, the function treats it as its argument, and says that it is invalid as it expects a number (the true underlying value). 21/4/2008 is not a number here, so it errors. If you want to enter the date into the WEEKDAY function, you have to force it into a number, either using another function such as you did with DATE, or coerce it directly, like =WEEKDAY(--"21/4/2008") or my preferred format of =WEEKDAY(--"2008-04-21") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Regarding WEEKDAY(), I read that problems can occur if dates are entered as text. Based on this, it is understandable that =WEEKDAY(2/14/2008) returns a wrong result (7). This is because general format is same as text. If I enter =WEEKDAY(DATE(2008,2,14)), I get the correct result (5). Okay, so far. What I don't understand is the following. I click A1 and key in 2/14/2008, then in A2, I key in =WEEKDAY(A1). I also get the correct result (5). The way I enter 2/14/2008 to A1 is exactly the same as I enter 2/14/2008 *directly* to the formula. It amazes me that referencing A1 in WEEKDAY() gives me the correct answer whereas keying it in as part of the formula won't work. Comments welcome. Epinn |
#9
|
|||
|
|||
WEEKDAY()
the N and the T on my keyboard are sicky sorry sTicky
So I meant to write "weekday(21/4/2008) doesN'T give an error" Steve On Sat, 09 Sep 2006 02:15:26 +0100, SteveW wrote: Because weekday(21/4/2008) does give an error as it treats 21/4/2008 as 21/4 divided by 2008 - nearly 0 ie 0/1/1900 in date terms Odd but thats what's happening Steve On Sat, 09 Sep 2006 01:52:22 +0100, Epinn wrote: Bob, Glad I still caught you this late. Thank you for enlightening me. 21/4/2008 is not a number here, so it errors. If it gives me an error, then I know. The problem is it returns "7" instead of "5" in my other example. That's very misleading and dangerous. =WEEKDAY(--"21/4/2008") gives #VALUE! =WEEKDAYS(--"4/21/2008") gives 2. So, I understand why you prefer =WEEKDAY(--"2008-04-21"). But I prefer "/" to "-" and I tested it. I am glad that "/" works too. My preference will be =WEEKDAY(--"2008/4/21"). I assume "--" above is the same as "--" in SUMPRODUCT(). Please confirm. Can't find double negating in Help. I find date functions may be as confusing as SUMPRODUCT(). Will see. Epinn "Bob Phillips" wrote in message ... But the difference is that when you key 21/4/2008 into cell A1, Excel recognises it as a date and converts it to an underlying value of 39559, which it presents/formats as that date. When you enter it into a function, the function treats it as its argument, and says that it is invalid as it expects a number (the true underlying value). 21/4/2008 is not a number here, so it errors. If you want to enter the date into the WEEKDAY function, you have to force it into a number, either using another function such as you did with DATE, or coerce it directly, like =WEEKDAY(--"21/4/2008") or my preferred format of =WEEKDAY(--"2008-04-21") -- Steve (3) |
#10
|
|||
|
|||
WEEKDAY()
In A2 enter:
=A1 *Then* format A2 to mmmm! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "MartinW" wrote in message ... Thanks Bob, of course it is I should have seen that...duh! So to get a return of September I can see a way of doing it by hiding the month cell and a Vlookup table somewhere out of the way and using a Vlookup in A2. Is there a simpler method? Thanks again Martin |
Thread Tools | |
Display Modes | |
|
|