WEEKDAY

  • Thread starter Thread starter HK
  • Start date Start date
H

HK

In A1 I have: 26-Feb-2010
Formula:
=INDEX({"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"};WEEKDAY(A1,2))
returns Friday
whereas formula:
=TEXT(WEEKDAY(A1,2),"dddd") returns Thursday.
(Second argument in WEEKDAY is 2 because here Monday is first day of week)

Why is it that the two formulas return different results?

Hans Knudsen
 
HK said:
In A1 I have: 26-Feb-2010
Formula:
=INDEX({"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"};WEEKDAY(A1,2))
returns Friday
whereas formula:
=TEXT(WEEKDAY(A1,2),"dddd") returns Thursday.
(Second argument in WEEKDAY is 2 because here Monday is first day of week)

Why is it that the two formulas return different results?

Hans Knudsen
Click to expand...


Try this instead:

=TEXT(A1,"dddd")
 
HK said:
Why is it that the two formulas return different results?
Click to expand...

Because WEEKDAY(A1,2) is 5, and 5 Jan 1900 was Thursday.

Try TEXT(A1,"dddd").

The point is: TEXT(...,"dddd") expects a date "serial number" in the first
parameter. The number 5 is the date serial number for 5 Jan 1900.

WEEKDAY also expects a date "serial number" in the first parameter. You
provided that in A1, namely the date serial number for 26 Feb 2010.


----- original message -----
 
WEEKDAY returns a number, not a date, so your TEXT function call evaluates
to this...

=TEXT(5,"dddd")

but "dddd" is used for dates, not numbers. I think you just want this...

=TEXT(A1,"dddd")
 
Because WEEKDAY(A1,2) is 5, and 5 Jan 1900 was Thursday.
Click to expand...

That's not quite the case. In point of fact, 5 Jan 1900 was a Friday :-))

But Excel thinks it was a Thursday!

This is one of the consequences of the Excel bug (which was said by some to
have been introduced in order to mimic a bug in the then dominant spreadsheet
program Lotus 1-2-3; although I suspect that may be just a CYA explanation)
which indicates falsely that 1900 was a leap year. So all the days prior to
Mar 1, 1900 are "off by one".
--ron
 
Forgot to mention that I had already used =TEXT(A1;"dddd"), I just wondered
why the two formulas returned something different.
Thanks to all.

Hans
 
Hi Ron
On a second thought I see it like this:

With 27-Feb-2010 i cell A1 the formula =TEXT(WEEKDAY(A1,1),"dddd") returns
Saturday (DK: lørdag) which of course is correct. But I think that this
correctness is a consequence of two (according to DK standards) false
assumptions. First the weekday part of the formula should be WEEKDAY(A1;2)
because here Monday is the first day of thee week. The second thing is that
(as far as I can see) Excel always, irrespective of location, consider
Sunday as the first day of the week. If i F9-analyze the WEEKDAY(A1;1) part
of above formula with 27-Feb-2010 in A1 I see 7. But here Saturday is
weekday number 6.

That's why
=INDEX({"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"};WEEKDAY(A1,2)
also returns correct result because here I use 2 as second argument in
WEEKDAY function and Monday as first day of week.

Are we saying the same or do you think I am wrong?

Hans Knudsen
 
Hi Ron
On a second thought I see it like this:

With 27-Feb-2010 i cell A1 the formula =TEXT(WEEKDAY(A1,1),"dddd") returns
Saturday (DK: lørdag) which of course is correct. But I think that this
correctness is a consequence of two (according to DK standards) false
assumptions. First the weekday part of the formula should be WEEKDAY(A1;2)
because here Monday is the first day of thee week. The second thing is that
(as far as I can see) Excel always, irrespective of location, consider
Sunday as the first day of the week. If i F9-analyze the WEEKDAY(A1;1) part
of above formula with 27-Feb-2010 in A1 I see 7. But here Saturday is
weekday number 6.

That's why
=INDEX({"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"};WEEKDAY(A1,2)
also returns correct result because here I use 2 as second argument in
WEEKDAY function and Monday as first day of week.

Are we saying the same or do you think I am wrong?
Click to expand...

I think you are wrong and are not understanding, among other things, how Excel
stores dates.

But I don't have time right now to explain. I'll check back later.
--ron
 
Back
Top