Home 
Search 
Today's Posts 
#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("20080421")  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("20080421"). 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("20080421")  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("20080421"). 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("20080421")  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("20080421")  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("20080421"). 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("20080421")  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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Function to write Weekday  Monday, Tuesday etc  Excel Discussion (Misc queries)  
Forecasting weekday occupancy between two years  Excel Worksheet Functions  
Conditional formatting using weekday  Excel Discussion (Misc queries)  
weekday display in excel  Excel Worksheet Functions  
Weekday Function  Excel Worksheet Functions 