WEEKDAY()

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

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

Hi Bob,

Can you also explain another one along similar lines.
If I put todays date 09/09/2006 in A1.
Then in A2 I put =MONTH(A1) and I get 9 which is good.
I then format A2 as mmmm and I get January. What gives??

Confused
Martin

Again it is because the underlying values of dates are just numbers. As I
said to Epinn, the value stored in a date of 21/4/2008 is 39559.

Now when you do a =MONTH(A1) in A2, you are not really returning a date, but
a simple month number (9 in the case quoted). But, because it is a number,
if you do date type things on it, Excel will not complain, it will just work
on whatever date that number resolves to. As a date is stored as the number
of days since 1st January 1900, the value of 9 will be treated as 9th Jan
1900, so if you format it as mmmm, you get January. Format it as dd/mm/yyyy,
and see what I mean.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MartinW" wrote in message
...
Hi Bob,

Can you also explain another one along similar lines.
If I put todays date 09/09/2006 in A1.
Then in A2 I put =MONTH(A1) and I get 9 which is good.
I then format A2 as mmmm and I get January. What gives??

Confused
Martin

Bob,

Glad I still caught you this late. Thank you for enlightening me.

21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7" instead of "5" in my other example. That's very misleading and dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please confirm. Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

"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

Thanks Bob, of course it is I should have seen that...duh!
So to get a return of September I can see a way of doing it by
hiding the month cell and a Vlookup table somewhere out of the
way and using a Vlookup in A2. Is there a simpler method?

Thanks again
Martin

Because weekday(21/4/2008) does give an error
as it treats 21/4/2008 as 21/4 divided by 2008 - nearly 0
ie 0/1/1900 in date terms
Odd but thats what's happening

Steve




On Sat, 09 Sep 2006 01:52:22 +0100, Epinn
wrote:

Bob,

Glad I still caught you this late. Thank you for enlightening me.

21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7"
instead of "5" in my other example. That's very misleading and
dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too..

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please
confirm. Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

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




--
Steve (3)

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

the N and the T on my keyboard are sicky sorry sTicky
So I meant to write "weekday(21/4/2008) doesN'T give an error"

Steve

On Sat, 09 Sep 2006 02:15:26 +0100, SteveW
wrote:

Because weekday(21/4/2008) does give an error
as it treats 21/4/2008 as 21/4 divided by 2008 - nearly 0
ie 0/1/1900 in date terms
Odd but thats what's happening

Steve




On Sat, 09 Sep 2006 01:52:22 +0100, Epinn
wrote:

Bob,

Glad I still caught you this late. Thank you for enlightening me.

21/4/2008 is not a number here, so it errors.

If it gives me an error, then I know. The problem is it returns "7"
instead of "5" in my other example. That's very misleading and
dangerous.

=WEEKDAY(--"21/4/2008") gives #VALUE!
=WEEKDAYS(--"4/21/2008") gives 2.

So, I understand why you prefer =WEEKDAY(--"2008-04-21").

But I prefer "/" to "-" and I tested it. I am glad that "/" works too.

My preference will be =WEEKDAY(--"2008/4/21").

I assume "--" above is the same as "--" in SUMPRODUCT(). Please
confirm. Can't find double negating in Help.

I find date functions may be as confusing as SUMPRODUCT().

Will see.

Epinn

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







--
Steve (3)

In A2 enter:
=A1
*Then* format A2 to mmmm!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MartinW" wrote in message
...
Thanks Bob, of course it is I should have seen that...duh!
So to get a return of September I can see a way of doing it by
hiding the month cell and a Vlookup table somewhere out of the
way and using a Vlookup in A2. Is there a simpler method?

Thanks again
Martin