Week Numbers In Excel
This page describes how to work with week numbers in Excel.
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.
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 = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function
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 = Int(((DT - DateSerial(Year(DT), 1, 1) + _
WSMod(DayOfWeek - Weekday(DateSerial(Year(DT), 1, 1)), 7)) + 6) / 7)
End Function
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
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
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
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
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 = Number - Divisor * Int(Number / Divisor)
End Function
|
This page last updated: 3-September-2009. |