Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Hi,
I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Hi,
I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
try this in your code
msgbox format(myDate,"dddd, mmm dd, yyyy") to see if your date is being interpreted correctly. Try it with an ambigous date combination like 06/12/05 for Dec 6th. If it isn't correct, then try converting your date string with cDate Dim myDate as Date dim myStr as String myStr = "06/12/05" myDate = cDate(myStr) msgbox format(myDate,"dddd, mmm dd, yyyy") -- Regards, Tom Ogilvy "GLT" wrote in message ... Hi, I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Try:
Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) "Tom Ogilvy" wrote: try this in your code msgbox format(myDate,"dddd, mmm dd, yyyy") to see if your date is being interpreted correctly. Try it with an ambigous date combination like 06/12/05 for Dec 6th. If it isn't correct, then try converting your date string with cDate Dim myDate as Date dim myStr as String myStr = "06/12/05" myDate = cDate(myStr) msgbox format(myDate,"dddd, mmm dd, yyyy") -- Regards, Tom Ogilvy "GLT" wrote in message ... Hi, I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Since the default for your added argument is 1 as well, did this correct it
for you? Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) ? myday FR While making sure the date was interpreted correctly: Mydate="12/14/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) ? myday WE Mydate="12/14/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate)), 2)) ? myday WE didn't react differently to the specified argument (default value the same). Still it is not clear how US English is misinterpreting 14/12/05 although this gives a hint: ? dateValue("14/12/05") 12/05/2014 ? weekday(datevalue("14/12/05")) 6 Excel 2003 US English -- Regards, Tom Ogilvy "Toppers" wrote in message ... Try: Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) "Tom Ogilvy" wrote: try this in your code msgbox format(myDate,"dddd, mmm dd, yyyy") to see if your date is being interpreted correctly. Try it with an ambigous date combination like 06/12/05 for Dec 6th. If it isn't correct, then try converting your date string with cDate Dim myDate as Date dim myStr as String myStr = "06/12/05" myDate = cDate(myStr) msgbox format(myDate,"dddd, mmm dd, yyyy") -- Regards, Tom Ogilvy "GLT" wrote in message ... Hi, I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Thanks Tom & Toppers,
Tom the varible myDate was already converted using cdate (sorry I forgot to mention it earlier)... Toppers your suggestions worked perfectly thanks... "Toppers" wrote: Try: Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) "Tom Ogilvy" wrote: try this in your code msgbox format(myDate,"dddd, mmm dd, yyyy") to see if your date is being interpreted correctly. Try it with an ambigous date combination like 06/12/05 for Dec 6th. If it isn't correct, then try converting your date string with cDate Dim myDate as Date dim myStr as String myStr = "06/12/05" myDate = cDate(myStr) msgbox format(myDate,"dddd, mmm dd, yyyy") -- Regards, Tom Ogilvy "GLT" wrote in message ... Hi, I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Tom,
With mydate set to "14/12/05" or "12/14/05" I obtained the same results i.e "WE" and correct interpretation the date. (14th Dec 2005). Removing the 1 gave me a result of "TH". Your "ambiguous" date of "06/12/05" was interpreted correctly (6th Dec 2005) and "TU" while "05/12/06") was interpreted correctly as 5th Dec 2006, also a "TU". Again, removing the 1! gave n incorrect result of "WE2 in both cases. I have Excel 2003 with UK date format. Sub a() mydate = "12/ 14 /05" Debug.Print Format(mydate, "dd/mm/yyyy") myday = UCase(Left(WeekdayName(Weekday(mydate), , 1), 2)) Debug.Print myday End Sub "Tom Ogilvy" wrote: Since the default for your added argument is 1 as well, did this correct it for you? Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) ? myday FR While making sure the date was interpreted correctly: Mydate="12/14/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) ? myday WE Mydate="12/14/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate)), 2)) ? myday WE didn't react differently to the specified argument (default value the same). Still it is not clear how US English is misinterpreting 14/12/05 although this gives a hint: ? dateValue("14/12/05") 12/05/2014 ? weekday(datevalue("14/12/05")) 6 Excel 2003 US English -- Regards, Tom Ogilvy "Toppers" wrote in message ... Try: Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) "Tom Ogilvy" wrote: try this in your code msgbox format(myDate,"dddd, mmm dd, yyyy") to see if your date is being interpreted correctly. Try it with an ambigous date combination like 06/12/05 for Dec 6th. If it isn't correct, then try converting your date string with cDate Dim myDate as Date dim myStr as String myStr = "06/12/05" myDate = cDate(myStr) msgbox format(myDate,"dddd, mmm dd, yyyy") -- Regards, Tom Ogilvy "GLT" wrote in message ... Hi, I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Day of Week from Date in VBA
Sounds like a bug in the UK version then.
-- Regards, Tom Ogilvy "Toppers" wrote in message ... Tom, With mydate set to "14/12/05" or "12/14/05" I obtained the same results i.e "WE" and correct interpretation the date. (14th Dec 2005). Removing the 1 gave me a result of "TH". Your "ambiguous" date of "06/12/05" was interpreted correctly (6th Dec 2005) and "TU" while "05/12/06") was interpreted correctly as 5th Dec 2006, also a "TU". Again, removing the 1! gave n incorrect result of "WE2 in both cases. I have Excel 2003 with UK date format. Sub a() mydate = "12/ 14 /05" Debug.Print Format(mydate, "dd/mm/yyyy") myday = UCase(Left(WeekdayName(Weekday(mydate), , 1), 2)) Debug.Print myday End Sub "Tom Ogilvy" wrote: Since the default for your added argument is 1 as well, did this correct it for you? Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) ? myday FR While making sure the date was interpreted correctly: Mydate="12/14/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) ? myday WE Mydate="12/14/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate)), 2)) ? myday WE didn't react differently to the specified argument (default value the same). Still it is not clear how US English is misinterpreting 14/12/05 although this gives a hint: ? dateValue("14/12/05") 12/05/2014 ? weekday(datevalue("14/12/05")) 6 Excel 2003 US English -- Regards, Tom Ogilvy "Toppers" wrote in message ... Try: Mydate="14/12/05" myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2)) "Tom Ogilvy" wrote: try this in your code msgbox format(myDate,"dddd, mmm dd, yyyy") to see if your date is being interpreted correctly. Try it with an ambigous date combination like 06/12/05 for Dec 6th. If it isn't correct, then try converting your date string with cDate Dim myDate as Date dim myStr as String myStr = "06/12/05" myDate = cDate(myStr) msgbox format(myDate,"dddd, mmm dd, yyyy") -- Regards, Tom Ogilvy "GLT" wrote in message ... Hi, I found the answer in a post that I already posted: myDay = UCase(Left(WeekdayName(Weekday(myDate)), 2)) One problem I am having is that the above returns the next day (ie. if myDate = 14/12/05 - which is Wednesday, myDay returns 'TH'. Anyone have any ideas why this is happening? "GLT" wrote: Hi, I have a msgbox that pops up when the user opens an excel worksheet and asks for a specific date in the [dd/mm/yy] forrmat and stores this in a varible called myDate. What I would like to do is extract the first two letters of the day from this date. So if the date input is 15/12/05, then TH (for Thursday) would be returned. Can anyone help with the VBA to acheive this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Display "this week" column headers w/date & day of week? | Excel Worksheet Functions | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |