Convert Week Number into Date

Ever receive a spreadsheet report and all the headers showed the week number? I don't know about you, but I don't know what Week 15 actually is. I'd have to get out my calendar and count the weeks. And what if you need to look at a past year? What we need is a nice little function that will convert Week ## Year into the date of the Monday for that week, as shown in Figure 4.12.

Weekday(Str) The argument is

Str—The Week information to be converted.

The result must be formatted as a date.

Function example:

Function ConvertWeekDay(Str As String) As Date Dim Week As Long Dim FirstMon As Date Dim TStr As String

FirstMon = DateSerial(Right(Str, 4), 1, 1) FirstMon = FirstMon - FirstMon Mod 7 + 2 TStr = Right(Str, Len(Str) - 5) Week = Left(TStr, InStr(1, TStr, " ", 1)) + 0 ConvertWeekDay = FirstMon + (Week - 1) * 7 End Function

Figure 4.12

Convert a week number into a date more easily referenced.

Figure 4.12

Convert a week number into a date more easily referenced.

0 0

Post a comment