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
|