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 wrote:
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


Try this instead:

=TEXT(A1,"dddd")

"HK" wrote:
Why is it that the two formulas return different results?

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 -----

"HK" wrote in message
...
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

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")

--
Rick (MVP - Excel)


"HK" wrote in message
...
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

On Fri, 26 Feb 2010 11:58:32 -0800, "Joe User" <joeu2004 wrote:

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

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



"Rick Rothstein" skrev i meddelelsen
...
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")

--
Rick (MVP - Excel)


"HK" wrote in message
...
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

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



"Ron Rosenfeld" skrev i meddelelsen
...
On Fri, 26 Feb 2010 11:58:32 -0800, "Joe User" <joeu2004 wrote:

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

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

On Sat, 27 Feb 2010 08:11:51 +0100, "HK" wrote:

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?

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