Extract Day of Week from Date in VBA

[Guest]

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.

 

[Guest]

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?

 

[Tom Ogilvy]

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

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?

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.

 

[Guest]

Try:

Mydate="14/12/05"
myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2))

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

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?

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.

 

[Tom Ogilvy]

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

Try:

Mydate="14/12/05"
myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2))

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

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?

:

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.

 

[Guest]

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

Try:

Mydate="14/12/05"
myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2))

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

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?

:

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.

 

[Guest]

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

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

Try:

Mydate="14/12/05"
myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2))

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



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?

:

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.

 

[Tom Ogilvy]

Sounds like a bug in the UK version then.

--
Regards,
Tom Ogilvy

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

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

Try:

Mydate="14/12/05"
myDay = UCase(Left(WeekdayName(Weekday(Mydate), , 1), 2))


:

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



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?

:

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.