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

  • Thread starter Thread starter Kobayashi
  • Start date Start date
K

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
 
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.
 
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
 
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.
 
Hi Adrian,

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

?weekdayname(weekday("02/06/2004",0))
Wednesday
 
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
 
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
 
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.
 
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

Tom Ogilvy said:
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.
Click to expand...
 
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
 
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
Click to expand...

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

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


--ron
 
Weekday returns a number, but weekdayname is looking for a date.
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top