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.
|