I better clarify for those who haven't read the other thread. "Anal" is a compliment by _ _ _ and I just carried it over. Hope using the word here hasn't offended anyone. ;)

Epinn

"Epinn" wrote in message ...
Biff,

Thank you for taking the time to explain things to me, another "perfectionist." Unfortunately, I am learning how to run before I can walk steadily. Bob is right and I am having an episode of "information overload" at this time. I am sure that I can appreciate your analysis even better in the future and I look forward to more. By the way, "anal" is good, and I don't feel like I am the only one being detailed. All good programmers have a sense of perfection.

Cheers,

Epinn

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