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.

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.

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.

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.

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.

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.

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.

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.