[EXCEL] Week Number

Introduction

Many applications and organizations use the week number of a date for some sort of identification, classification, or formatting purpose. However, what constitutes a week number, specifically when does Week 1 begin, is not always the same from one organization or application to another. This page describes worksheet formulas and VBA procedures that you can use to calculate a week number in a number of different manners.

The formulas and functions described on this page work with the following definitions of the first day of week 1 of a year:

• The first day of Week 1 is January of that year, regardless of what day of the week that might be.
• The first day of Week 1 is the first occurrence of some specified day of the week. For example, you may define Week 1 to begin on the first Monday of the year.
• The first day of Week 1 is a specific date. For example, you may define that Week 1 always begins on May 1 of the year.
• The first day of Week 1 is the first occurrence of one day of the week following the first occurrence of another day of the week. For example, you may define that Week 1 begins on the first Monday after the first Thursday of the year.
• The first day of Week 1 is specified by the International Standards Organization (ISO) definition.

Absolute Week Number

This is the simplest method. It assumes that Week 1 begins on January 1, regardless of what day of the week that might be. The formula for this is:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

where StartDate is the date whose week number is to be returned.

The VBA code for this function is:

Public Function WeekNumberAbsolute(DT As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberAbsolute
' This returns the week number of the date in DT based on Week 1 starting
' on January 1 of the year of DT, regardless of what day of week that
' might be.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,0))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WeekNumberAbsolute = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function

Week Number First Day Of Week

This method uses as the first day of Week 1 the first occurrence of a specified day of the week. For example, you might define that Week 1 begins on the first Monday of the year. The formula is:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+
MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7)

where StartDate is the whose week number is to be found, and DayOfWeek is the day of week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

The VBA code for this function is: 

Public Function WeekNumberFromFirstDayOfWeek(DT As Date, DayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromFirstDayOfWeek
' This returns the week number of the date DT based on Week 1 starting
' on the first DayOfWeek of the year of DT.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YEAR(DT),1,1)),7))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WeekNumberFromFirstDayOfWeek = Int(((DT - DateSerial(Year(DT), 1, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(Year(DT), 1, 1)), 7)) + 6) / 7)
End Function

Week Number From Date

This method uses a specific date as the first day of Week 1. The formula is:

=TRUNC(((TestDate-StartDate)+6)/7)+(WEEKDAY(TestDate)=WEEKDAY(StartDate))

where TestDate is the date whose week number is to be calcuatled, and StartDate specifies the first day of Week 1.

The VBA code for this function is:

Public Function WeekNumberFromDate(DT As Date, StartDate As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDate
' This returns the week number base on Week 1 starting on StartDate.
'       =TRUNC(((DT-StartDate)+6)/7)+(WEEKDAY(DT)=WEEKDAY(StartDate))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   WeekNumberFromDate = Int(((DT - StartDate) + 6) / 7) + Abs(Weekday(DT) = Weekday(StartDate))
End Function

Week Number From First Day Of Week Following Another Day Of Week

This methods uses as the first day of Week 1 the first occurrence of one specified day of the week that follows the first occurrence of another day of the week. For example, you might want the first day of week to be the Monday following the first Thursday of the year. The formula is:

=TRUNC(((TestDate-((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7))))+6)/7)+
(WEEKDAY(TestDate)=WEEKDAY(((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7)))))

where TestDate is the date whose week number is to be calculated. StartDayOfWeek is the number of the day of the week after which BaseDate is to be found. BaseDayOfWeek is the weekday number of the day following the first occurrence of BaseDayOfWeek that week 1 should begin. For example, to use the first Monday following the first Thursday of the year, BaseDayOfWeek should be 2 and StartDate should be 5.

The VBA code for this function is: 

Public Function WeekNumberFromDayOfWeekDay(DT As Date, BaseDayOfWeek As VbDayOfWeek, _
    StartDayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDayOfWeekDay
' This returns the week number based on Week 1 starting on the first
' StartDayOfWeek following the first BaseDayOfWeek day of week in the
' year of DT. E.g., the week number of 8-March-2009 based on Week 1
' beginning on the first StartDayOfWeek = Wednesday following the first
' BaseDayOfWeek = Sunday of the year 2009. That is, find the first Sunday,
' go to the next Wednesday, and begin Week 1 on that Wednesday.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FirstDayOfYear As Long
Dim FirstDayOfYearWeekday As Long

FirstDayOfYear = DateSerial(Year(DT), 1, 1)
FirstDayOfYearWeekday = Weekday(FirstDayOfYear)

WeekNumberFromDayOfWeekDay = Int(((DT - ((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))) + 6) / 7) + _
        (Weekday(DT) = Weekday(((FirstDayOfYear + WSMod(BaseDayOfWeek - _
        FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))))

End Function

ISO Week Number

This method uses the International Standards Organization (ISO) definition of a week number. An ISO week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday. Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.

Note that it is possible that the first few days of a year may fall into week 52 or week 53. Although this may seem quite counter intuitive, it follows from the requirements first that all ISO weeks begin on Monday, and second, that the first week must contain a Thursday. For example, the year 2010 begins on Friday, 1-Jan-2010. Since this is later than a Thursday (which is 7-Jan-2010), the week doesn't contains Thursday (or, said another way, the week doesn't have at least four days), the ISO week 1 begins on Monday, 4-January-2010. The day before the start of the year, 31-December-2009, falls in ISO week 53 of the preceeding year, 2009. Rather than deal with the ambiguity of having a day that is in both week 53 and week 0, the 53 carries through until 4-Jan-2010, which as noted before is the Monday of the first week that contains a Thursday (or, equivalently, the first week with four or more days).

The VBA code for calculating an ISO week number is shown below:

Public Function IsoWeekNumber(InDate As Date) As Long
    IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
End Function

The WSMod Function

To maintain a symetry between the VBA and the Formula implementaitons of the various functions, the code uses a function named WSMod that works the way Excel's MOD works. This is different that VBA's Mod operator. 

Private Function WSMod(Number As Double, Divisor As Double) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WSMod
' The Excel worksheet function MOD and the VBA Mod operator
' work differently and can return different results under
' certain circumstances. For continuity between the worksheet
' formulas and the VBA code, we use this WSMod function, which
' produces the same result as the Excel MOD worksheet function,
' rather than the VBA Mod operator.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WSMod = Number - Divisor * Int(Number / Divisor)
End Function