CODEWITHSUNDEEP
excelvbadate
user7415328
user7415328

Reputation: 1083

vba get weekday name of date? UK date format issue

I am using the following vba code to get the weekday of a date.

Dim strDate
strDate = "01/06/2017"
wStemplaTE.Range("C25").value = WeekdayName(Weekday(DateValue(strDate)))

This should produce : Thursday but i am getting Friday. I think this is because excel thinks my date is US format. Bu It's UK format.

Please can someone show me where i am going wrong?

Upvotes: 0

Views: 2625

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

Dim strDate

By Hungarian Notation standards you're calling it a String, but it's declared as a[n implicit] Variant.

Dim theDate As Date

This declares theDate as a Date.

For unambiguous date handling, treat dates as dates, not as strings - and try to avoid Variant whenever you can.

Then use the ISO standard yyyy-MM-dd format when you want to assign a date literal (using # delimiters):

theDate = #2017-06-01#

That way you don't need to take your Variant/String and turn it into a Date - it's already a date.

wStemplaTE.Range("C25").value = WeekdayName(Weekday(theDate))

Gives you Thursday as it should.

This would, too:

wStemplaTE.Range("C25").value = Format(theDate, "dddd")

Edit: just read the comments under the question - going forward please put all the crucially relevant information in the question itself. @Jeeped's answer works if there's no date literal, variant or string involved.

Since the date is actually in a cell, and you're writing the weekday name in another cell, I'd suggest an all-Excel approach:

=TEXT([theDate],"dddd")

Returns the weekday name off the long date format, as text.

There are other approaches too, if you really want to work off the WEEKDAY - e.g. a lookup table:

index/match lookup using a lookup table

Make a table somewhere (here I called it wkDays), and use the return value of the WEEKDAY function to lookup the weekday name.

=WEEKDAY([thedate],1)

Returns 5 for Thursday.

=INDEX(wkDays[Name],MATCH(WEEKDAY([thedate],1),wkDays[WkDay],0))

Returns Thursday because the wkDays table maps Thursday to the value 5.

Upvotes: 2

user4039065
user4039065

Reputation:

Despite being on a MDY regiona;l system, I put 01-Jun-2017 into A1 on Sheet2 and used a custom number format of dd/mm/yyyy. Either of these produces the correct weekday name (e.g. Thursday).

Debug.Print WeekdayName(Weekday(Worksheets("Sheet2").Range("A1").Value2))
Debug.Print Format(Worksheets("Sheet2").Range("A1").Value2, "dddd")

This works because a date can be considered a raw number. 01-Jun-2017 happens to be 42,887.

Upvotes: 2

Related Questions