Re: WEEKDAY()

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Probably the reason 21/4/2008 doesn't work for you is because your regional
settings aren't set-up as d/m/y, BUT as m/d/y.

It *would* work on Bob's machine because he probably has the European short
date set-up in his regional settings.

And of course, 2008/04/21 should work *everywhere*, since it's recognized as
international.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" <someone@xxxxxxxxxxxxxxxxxxx> wrote in message
news:exf8Ip60GHA.4312@xxxxxxxxxxxxxxxxxxxxxxx
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" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:OZDC7P60GHA.4264@xxxxxxxxxxxxxxxxxxxxxxx
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" <someone@xxxxxxxxxxxxxxxxxxx> wrote in message
news:OCAu0E60GHA.2400@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: WEEKDAY()
    ... Thank you for enlightening me. ... I find date functions may be as confusing as SUMPRODUCT. ... and says that it is invalid as it expects a number (the true underlying ...
    (microsoft.public.excel.newusers)
  • Re: WEEKDAY()
    ... and says that it is invalid as it expects a number (the true underlying ... Since WEEKDAY only works with integers Excel truncates the value to 0. ...
    (microsoft.public.excel.newusers)
  • Re: WEEKDAY()
    ... and says that it is invalid as it expects a number (the true underlying ... Since WEEKDAY only works with integers Excel truncates the value to 0. ...
    (microsoft.public.excel.newusers)
  • Re: WEEKDAY()
    ... and says that it is invalid as it expects a number (the true underlying ... Since WEEKDAY only works with integers Excel truncates the value to 0. ...
    (microsoft.public.excel.newusers)