How to get correct week number in Access

Question

I get a large data set every week, with Start Date and End Date, Start Date is always Monday and End Date is always Sunday. I want to be able to do a rolling YTD on Sales for different products by week. I am trying to give each week's data a week number. But my first week is 12/28/2015-1/3/2016, it keeps giving me week 53 instead of week 1. I saw another person's post on the same issue and dbDesigner's answer actually works:

Get the week number from a given date

But this gives me 2016-01 in one column. I want two columns with one for correct week number and one for correct year to be able to do a sub query for my rolling YTD sales. For example for my first week 12/28/2015-1/3/2016, it will be week: 1 and year:2016

Thanks.

Do you understand the solution you refer to? Because if you do, you should notice that year and week are concatinated. So you should undo concatination. — Rene, Oct 01 '16 at 02:27
Well, thanks for the wake up. I separated them and they all worked. — Sarah, Oct 05 '16 at 21:29

score 0 · Answer 1 · answered Oct 04 '16 at 08:12

First, none of the week numbering methods in VBA follows the ISO 8601 standard.

Second, the week number of the week 2015-12-28 to 2016-01-03 is not the first of 2106 but 2015W53.

You can retrieve the correct ISO 8601 week number for any date with a function like this:

Public Function ISO_WeekYearNumber( _
  ByVal datDate As Date, _
  Optional ByRef intYear As Integer, _
  Optional ByRef bytWeek As Byte) _
  As String

' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.    Public Function ISO_WeekNumber( _
  ByVal datDate As Date) _
  As Byte

' Calculates and returns week number for date datDate according to the ISO 8601:1988 standard.
' 1998-2000, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53

  Dim bytWeek                   As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)

  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(Year(datDate), 12, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000 bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If

  ISO_WeekNumber = bytWeek

End Function
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  Const cbytMonthJanuary        As Byte = 1
  Const cbytMonthDecember       As Byte = 12
  Const cstrSeparatorYearWeek   As String = "W"

  Dim bytMonth                  As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  intYear = Year(datDate)
  bytMonth = Month(datDate)
  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)

  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000+ bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If

  ' Adjust year where week number belongs to next or previous year.
  If bytMonth = cbytMonthJanuary Then
    If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
      ' This is an early date of January belonging to the last week of the previous year.
      intYear = intYear - 1
    End If
  ElseIf bytMonth = cbytMonthDecember Then
    If bytWeek = cbytFirstWeekOfAnyYear Then
      ' This is a late date of December belonging to the first week of the next year.
      intYear = intYear + 1
    End If
  End If

  ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")

End Function

If you just need the week number itself, a function like this can be used:

Thanks, but I used the old post and it works fine now. I can get the correct week number and year number. — Sarah, Oct 05 '16 at 21:28

score 0 · Answer 2 · answered Oct 05 '16 at 21:32

0

Do you understand the solution you refer to? Because if you do, you should notice that year and week are concatinated. So you should undo concatination.

answered Oct 05 '16 at 21:32

Rene

1,095
1
8
17

How to get correct week number in Access

2 Answers2

Linked