View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Date Range of Week

That sounds like the ISO Week. So based on the page I gave you

Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If

End Function

will give you the date of Monday in the first week. You would then
mutilply
7 times the week number minus 1 and add it to that date to get the Monday
of
your week.


If I haven't made an error, here is a slightly different algorithm for
getting the start of year, coupled with the week number calculation, which
yields this function for the Monday of the week number specified...

Function StartOfWeek(TheYear As Long, WeekNumber As Long) As Date
Dim DayOne As Date
DayOne = "1/1/" & CStr(TheYear)
If DatePart("ww", DayOne, vbMonday, vbFirstFourDays) 1 Then
DayOne = DayOne + 7
End If
StartOfWeek = DayOne - Weekday(DayOne, vbTuesday) + 7 * WeekNumber
End Function


Rick