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