Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 "Kobayashi " wrote in message ... 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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 "Kobayashi " wrote in message ... 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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
Hi Adrian,
?weekdayname(weekday("26/05/2004",0)) Wednesday ?weekdayname(weekday("02/06/2004",0)) Wednesday --- Regards, Norman "Kobayashi " wrote in message ... 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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 "Kobayashi " schrieb im Newsbeitrag ... 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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 "Kobayashi " wrote in message ... 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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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" wrote in message ... 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 "Kobayashi " wrote in message ... 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/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
On Wed, 2 Jun 2004 08:05:30 -0500, Kobayashi
wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
On Wed, 2 Jun 2004 09:45:24 -0400, "Tom Ogilvy" wrote:
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect results of Weekdayname(Weekday... function???
On Wed, 2 Jun 2004 08:05:30 -0500, Kobayashi
wrote: 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)),,vbSund ay) Wednesday ?weekdayname(weekday(dateserial(2004,6,2),vbUseSys temDayOfWeek),,vbUseSystemDayOfWeek) Wednesday ?weekdayname(weekday(dateserial(2004,6,2)),,vbUseS ystemDayOfWeek) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goalseek gives incorrect results | New Users to Excel | |||
results incorrect | Excel Discussion (Misc queries) | |||
vlookup gives incorrect results | Excel Worksheet Functions | |||
WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
Very large workbook now giving incorrect results :( | Excel Discussion (Misc queries) |