Incorrect results of Weekdayname(Weekday... function???

[Kobayashi >]

I'm trying to determine the day of the week for a date, which will the
be used in an IF argument. However, when I put the following in th
debug pane I get 'Thursday' as the result, when clearly today i
Wednesday!???

I'm actually using a variable which contains the date of "26/05/2004"
but this doesn't work either?

I'm using Excel 2000 with UK regional settings.

?weekdayname(weekday("02/06/2004"))

I'm sure this is going to be something very simple and slightl
embarassing for me when resolved but help would be appreciated!

Very confused!

Adria

--
Message posted from http://www.ExcelForum.com

[Tom Ogilvy]

Weekday returns a number, but weekdayname is looking for a date.

Weekdayname(DateValue("05/26/2004"))

or
Weekdayname(#05/26/2004#)

In VBA you should keep your date strings in mm/dd/yyyy format.
--
Regards,
Tom Ogilvy

I'm trying to determine the day of the week for a date, which will then
be used in an IF argument. However, when I put the following in the
debug pane I get 'Thursday' as the result, when clearly today is
Wednesday!???
I'm actually using a variable which contains the date of "26/05/2004",
but this doesn't work either?
I'm using Excel 2000 with UK regional settings.
?weekdayname(weekday("02/06/2004"))
I'm sure this is going to be something very simple and slightly
embarassing for me when resolved but help would be appreciated!
Very confused!
Adrian
---
Message posted from http://www.ExcelForum.com/

[Kobayashi >]

Tom,

Many thanks for your reply! However, I've pasted what you've provide
in the debug pane and get either a type mismatch or invalid cal
error???

Regards,

Adria

--
Message posted from http://www.ExcelForum.com

[Tom Ogilvy]

Well, I am using Excel 2000 and this function isn't available there, but I
do understand what you are feeding your function using your present
construct.

? weekday(DateValue("05/26/2004"))
4
? format(weekday(DateValue("05/26/2004")),"dddd")
Wednesday

because a 4 is January 4, 1900

What I provided should logically work - but I don't have any way to test it.
--
Regards,
Tom Ogilvy

Tom,
Many thanks for your reply! However, I've pasted what you've provided
in the debug pane and get either a type mismatch or invalid call
error???
Regards,
Adrian
---
Message posted from http://www.ExcelForum.com/

[Kobayashi >]

Tom,

Thanks very much, the '
format(weekday(DateValue("05/26/2004")),"dddd")' function certainly di
the trick!

One last question if I may? I need my worksheets to be in uk dat
format: dd/mm/yyyy. However, you have made me a bit worried with you
remarks about using mm/dd/yyyy (which I know is better), but I pass on
of the date values to a variable which I will then use in the abov
function instead of the "05/26/2004" literal date value. Will
encounter problems? Can I easily change the format of the value of th
variable so that it becomes mm/dd/yyyy?

Many thanks,

Adria

--
Message posted from http://www.ExcelForum.com

[Tom Ogilvy]

Dim myDate as Date
myDate = Range("A1").Value

the best is to work with the date serial number. Then there is no
confusion. An alternative is to use an unambiguous format like

Jan 2, 2004

Dates are stored as the number of days from a base date. In Windows, the
default is 1900. For the Mac, 1904.

I don't know specifically where you will get your date from, so it is hard
to say what you should watch out for.
--
Regards,
Tom Ogilvy

Tom,
Thanks very much, the '?
format(weekday(DateValue("05/26/2004")),"dddd")' function certainly did
the trick!
One last question if I may? I need my worksheets to be in uk date
format: dd/mm/yyyy. However, you have made me a bit worried with your
remarks about using mm/dd/yyyy (which I know is better), but I pass one
of the date values to a variable which I will then use in the above
function instead of the "05/26/2004" literal date value. Will I
encounter problems? Can I easily change the format of the value of the
variable so that it becomes mm/dd/yyyy?
Many thanks,
Adrian
---
Message posted from http://www.ExcelForum.com/

[Tom Ogilvy]

Post by Tom Ogilvy
Well, I am using Excel 2000 and this function isn't available there,

Must have been a glitch. It is present in Excel 2000.

There isn't any reason

WeekdayName(weekday(DateValue("05/26/2004")))
shouldn't return the correct name.

? weekdayname(weekday(DateValue("05/26/2004")))
Wednesday

Both WeekdayName and Weekday have an optional second argument that specifies
what the first day of the week is, but if unspecified, they should both be
the same.

Not sure why you are getting bad results.
--
Regards,
Tom Ogilvy

Post by Tom Ogilvy
Well, I am using Excel 2000 and this function isn't available there, but I
do understand what you are feeding your function using your present
construct.
? weekday(DateValue("05/26/2004"))
4
? format(weekday(DateValue("05/26/2004")),"dddd")
Wednesday
because a 4 is January 4, 1900
What I provided should logically work - but I don't have any way to test it.
--
Regards,
Tom Ogilvy

Tom,
Many thanks for your reply! However, I've pasted what you've provided
in the debug pane and get either a type mismatch or invalid call
error???
Regards,
Adrian
---
Message posted from http://www.ExcelForum.com/

[Kobayashi >]

Thanks All!

Norman,

Cheers. I think this is what I was trying to do initially, and may eve
have stolen what I had from another posting of yours?! Anyway, I'v
tried it and it works - thanks! Although I've already put Tom's code i
so that will be staying in this instance, but I think I'll be able t
remember the weekdayname(weekday... function more easily.


Tom,

Thanks again! I get my date from a cell value but I must keep the cel
values in the dd/mm/yyyy format. This said, if this is ALWAYS the cas
will using my variable which contains the date in dd/mm/yyyy format b
okay as long as the worksheet cell values remain consistent?

My procedure is finished now, thanks to your help, and so I don't wan
to hog everybody's time but if it's going to fall over because of th
date format I obviously would like to know in advance and try to adjus
the code now?

Below is the code that passes the date to the variable:

Set Cobd = Rows(1).Find("closeofbusinessdate").Offset(1, 0)

Cheers.

Adria

--
Message posted from http://www.ExcelForum.com

[Ron Rosenfeld]

Post by Tom Ogilvy
Weekday returns a number, but weekdayname is looking for a date.

Not according to HELP:

WeekdayName(weekday, abbreviate, firstdayofweek)

weekday Required. The numeric designation for the day of the week. Numeric
value of each day depends on setting of the firstdayofweek setting.

The other two arguments are optional.


--ron

[Norman Jones]

Hi Adrian,

?weekdayname(weekday("26/05/2004",0))
Wednesday

?weekdayname(weekday("02/06/2004",0))
Wednesday


---
Regards,
Norman

I'm trying to determine the day of the week for a date, which will then
be used in an IF argument. However, when I put the following in the
debug pane I get 'Thursday' as the result, when clearly today is
Wednesday!???
I'm actually using a variable which contains the date of "26/05/2004",
but this doesn't work either?
I'm using Excel 2000 with UK regional settings.
?weekdayname(weekday("02/06/2004"))
I'm sure this is going to be something very simple and slightly
embarassing for me when resolved but help would be appreciated!
Very confused!
Adrian
---
Message posted from http://www.ExcelForum.com/

[Rudolf Sommer]

You have to specifiy your first day of week if it differs from sunday, which
is the default in Excel. You have to specify it twice, once for the weekday
function and again for the weekdayname function
Try
?weekdayname(weekday(datevalue("26/05/2004"),vbMonday),False,vbMonday)
vbMonday = first day of week
I have only the German version of Excel help, so I cannot paste it here.
Ruedi

I'm trying to determine the day of the week for a date, which will then
be used in an IF argument. However, when I put the following in the
debug pane I get 'Thursday' as the result, when clearly today is
Wednesday!???
I'm actually using a variable which contains the date of "26/05/2004",
but this doesn't work either?
I'm using Excel 2000 with UK regional settings.
?weekdayname(weekday("02/06/2004"))
I'm sure this is going to be something very simple and slightly
embarassing for me when resolved but help would be appreciated!
Very confused!
Adrian
---
Message posted from http://www.ExcelForum.com/

[Ron Rosenfeld]

On Wed, 2 Jun 2004 08:05:30 -0500, Kobayashi

I'm trying to determine the day of the week for a date, which will then
be used in an IF argument. However, when I put the following in the
debug pane I get 'Thursday' as the result, when clearly today is
Wednesday!???
I'm actually using a variable which contains the date of "26/05/2004",
but this doesn't work either?
I'm using Excel 2000 with UK regional settings.
?weekdayname(weekday("02/06/2004"))
I'm sure this is going to be something very simple and slightly
embarassing for me when resolved but help would be appreciated!
Very confused!
Adrian

VBA can be very US-Centric. I would suggest something like:

?weekdayname(weekday(dateserial(2004,6,2)))


--ron

[Ron Rosenfeld]

On Wed, 2 Jun 2004 08:05:30 -0500, Kobayashi

I'm trying to determine the day of the week for a date, which will then
be used in an IF argument. However, when I put the following in the
debug pane I get 'Thursday' as the result, when clearly today is
Wednesday!???
I'm actually using a variable which contains the date of "26/05/2004",
but this doesn't work either?
I'm using Excel 2000 with UK regional settings.
?weekdayname(weekday("02/06/2004"))
I'm sure this is going to be something very simple and slightly
embarassing for me when resolved but help would be appreciated!
Very confused!
Adrian

Here's something very interesting and I don't understand it.

With US regional settings:

?weekdayname(weekday(dateserial(2004,6,2)))
Wednesday

With UK regional settings:

?weekdayname(weekday(dateserial(2004,6,2)))
Thursday

but, still with UK regional settings:

?weekdayname(weekday(dateserial(2004,6,2),vbSunday),,vbSunday)
Wednesday

?weekdayname(weekday(dateserial(2004,6,2)),,vbSunday)
Wednesday

?weekdayname(weekday(dateserial(2004,6,2),vbUseSystemDayOfWeek),,vbUseSystemDayOfWeek)
Wednesday

?weekdayname(weekday(dateserial(2004,6,2)),,vbUseSystemDayOfWeek)
Thursday

and finally, with UK settings:

?weekdayname(1)
Monday

but with US settings:

?weekdayname(1)
Sunday

So it seems that the safest method is to both generate a date serial number
from an unambiguous entry; and also to explicitly specify the first day of the
week for both the weekday and weekdayname arguments.


--ron