Hi All-
I have a table listing Week Numbers. For instance, this week of the year is
Week 25.
What I would like to do is print the Date Range for the specific Week
Number. i.e.; "Week 25 is June 18 - June 24, 2007".
I hope that makes sense.
Thanks for any help you can provide.
Fred
There may be some canned function for it, but I couldn't find it.
Place this function in a standard module. Assign the control source of
a textbox on your report
to '=getWeekString(PutWeekNumberHere)'.
Oh, some warnings - I can deduce that your weeks begin on Monday from
the date range in your original post. What I can't tell is which
method of numbering weeks you are using (Is week # 1 the first FULL
week, or just the week that starts with January 1?). The function
provided uses the first full week.
Public Function getWeekString(intWeek As Integer, Optional intYear As
Integer) As String
'Declarations
Dim dtTemp As Date
'Use the current year if not specified
If intYear = 0 Then intYear = Year(Date)
'Initialize our date to Jan 1
dtTemp = DateSerial(intYear, 1, 1)
'Find the Monday of the first week of the year.
dtTemp = DateAdd("d", -1 * (DatePart("w", dtTemp, vbMonday,
vbFirstFullWeek) - 1), dtTemp)
'Add the number of weeks desired
dtTemp = DateAdd("ww", intWeek - 1, dtTemp)
'Return the string in the format you desire
getWeekString = "Week " & intWeek & " is " _
& Format$(dtTemp, "mmmm d") & " - " _
& Format$(DateAdd("d", 6, dtTemp), "mmmm d, yyyy") & "."
End Function
Enjoy,
Kris
