Full Version: Function to convert a week number to month number
kellyrmartin
Does anyone know of a function that will take a week number and send back the month that most of that week occupies?
theDBguy
Hi,

I'm not aware of any. But if you create one, you should consider posting it in the Function Library.

Good luck!
Gustav

datLastWeekday = ISO_DateOfWeek(intYearOfYourWeek, bytWeeknumber, vbSunday)

Now find the day of that date:

bytDay = Day(datLastWeekday)

If this is larger than 3, your month is the month of datLastWeekday.
If not, your month is the previous month.
Or:

bytMonth = Month(DateAdd("m", Abs(Day(datLastWeekday) > 3) - 1, datLastWeekday))

Note that if you are not using the ISO 8601 numbering method for weeks you are in trouble.

CODE
Public Function ISO_DateOfWeek( _
ByVal intYear As Integer, _
ByVal bytWeek As Byte, _
Optional ByVal bytWeekday As Byte = vbMonday) _
As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

' The fourth of January is always included in
' the first week of year intYear.
Const cbytDayOfFirstWeek  As Byte = 4
' Number of days in a week.
Const cbytDaysOfWeek      As Byte = 7
' Month of January.
Const cbytJanuary         As Byte = 1

Dim datDateOfFirstWeek    As Date
Dim intISOMonday          As Integer
Dim intISOWeekday         As Integer
Dim intWeekdayOffset      As Integer

' No specific error handling.
On Error Resume Next

If intYear > 0 Then
' Weekday of Monday.
intISOMonday = WeekDay(vbMonday, vbMonday)
' Date of fourth of January in year intYear.
datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
' Weekday of fourth of January in year intYear.
intISOWeekday = WeekDay(datDateOfFirstWeek, vbMonday)
' Calculate offset from Monday in first week of year intYear.
intWeekdayOffset = intISOMonday - intISOWeekday

' Weekday of requested weekday.
intISOWeekday = WeekDay(bytWeekday, vbMonday)
' Calculate offset from requested weekday in first week of year intYear.
intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
' Date of requested weekday in first week of year intYear.

' Date of requested weekday in requested week of year intYear.
datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
End If

ISO_DateOfWeek = datDateOfFirstWeek

End Function

/gustav
Gustav
Oh, that wasn't too clever. You would, of course, look up the middle day of a week, Thursday, of that week as the month of this weekday would be the "Month of the week":

datWeekMidDay = ISO_DateOfWeek(intYearOfYourWeek, bytWeeknumber, vbThursday)

Then:

bytMonth = Month(datWeekMidDay)
intYear = Year(datWeekMidDay)

/gustav