Week Numbers In Excel
|Some business applications use week
numbers to categorize dates. For example, a business may report
sales amounts by week, and identify each period as "9912",
representing the 12th week of 1999. While this may be convenient in
some applications, you need to be careful when using week
numbers. When does week 1 start? On the first day of the year?
As the first full 7 day week of the year? The first week having more than
3 days? Moreover, what day does a week begin on? Sunday or
There is not much consistency among the Excel functions and VBA functions, and the various "standards" used in business. If you need to use week numbers, you need to understand how they work and what they represent.
Absolute Week Numbers
An absolute week number is the 7 day period that a date falls within, based solely on the first day of the year, regardless of the day of the week. Week 1 is always Jan-1 to Jan-7, week 2 is always Jan-8 to Jan-14, and so on. If the year begins on a Thursday, then each "week" is from Thursday to the following Wednesday. The absolute week number will always be between 1 and 53. Week 53 will have either one or two days, depending on whether the year is a leap year. If the year is a not a leap year, week 53 will consist of one day: Dec-31. If the year is a leap year, week 53 will consist of two days: Dec-30 and Dec-31.
Given a date in cell A1, you can determine the absolute week number with the following formula:
For example, if A1 contains the date 2000-June-21, the formula will return 25. Since the year 2000 began on a Saturday, the 25th week runs from Saturday, 2000-June-17, to Friday, 2000-June-23.
Excel Week Numbers
Excel itself does not have a function for returning a week number. The Analysis ToolPak add-in module does have a WEEKNUM function. This function return the week number of a date, based on a week starting on either Sunday or Monday. Therefore, week 1 may have anywhere from 1 to 7 days. For example, if you set the return type of WEEKNUM to 2, indicating that weeks begin on Monday, and the year is 2001, which begins on a Monday, week 1 will have 7 days. But for the year 2006, which begins on a Sunday, week 1 will have only 1 day. For example, if A1 contains the date 2000-Jan-9, a Sunday, the function
will return 2, as 2000-Jan-9 is the last day of the second week of 2000. Since the 2 in the formula indicates that weeks begin on Monday, week 2 is computed as Monday, 2000-Jan-3 through Sunday, 2000-Jan-9. If we change the formula to
it will return 3, since the 1 in the formula indicates that weeks begin on Sunday, and week 3 is computed as Sunday, 2000-Jan-9, through Saturday, 2000-Jan-15.
For the year 2000, WEEKNUM will compute week 1 as containing either only 1 day, Saturday, 1-Jan, if the return-type setting is 1 (weeks begin on Sunday), or as containing 2 days, Saturday, 1-Jan, and Sunday, 2-Jan, if the return-type setting is 2 (weeks begin on Monday).
You can achieve the same result in VBA code with the following function:
Function VBAWeekNum(D As Date, FW As Integer) As Integer
The FW parameter serves the same purpose here as it does in the WEEKNUM worksheet function. Set it to 1 to indicate that weeks begin on Sunday, or to 2 to indicate that weeks begin on Monday.
ISO Week Numbers
The International Organization for Standardisation, based in Switzerland, issued Standard 8601 -- Representation Of Dates And Times, in 1988. This provides some standardization for "week numbers". Of course, compliance with these standards is entirely voluntary, so your business may or may not use the ISO definitions.
Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.
While this provides some standardization, it can lead to unexpected results -- namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.
An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.
Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec.
To compute the ISO week number of a date in cell A1, use the following formula, provided by Laurent Longre. Note that this formula will not always return the correct value if you are using the 1904-based date system.
The following formula, developed by Evert van den Heuvel, works with either the 1900 or 1904 based date system.
In VBA, you can use the following function, written by John Green.
Public Function ISOWeekNum(AnyDate As Date,
This function requires the YearStart function, also written by John Green, which returns the date of the first Monday of a given year.
Function YearStart(WhichYear As Integer) As Date
As you can see, Week Numbers are not as simple as they may appear on the surface. They may be useful for some business applications, but it is important that you understand just what is being calculated, and what it really means. If your business uses week numbers, make sure you understand what the basis for week 1 represents.