UtterAccess Discussion Forums > Function to convert a week number to month number

Full Version: Function to convert a week number to month number

UtterAccess Discussion Forums > Microsoft® Access > Access Date + Time

kellyrmartin

Sep 3 2010, 04:24 PM

Does anyone know of a function that will take a week number and send back the month that most of that week occupies?

theDBguy

Sep 3 2010, 04:31 PM

Hi,

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

Good luck!

Gustav

Sep 3 2010, 04:38 PM

You can start with the function below.

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.
    datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)

    ' 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

Sep 4 2010, 12:36 AM

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

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.