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