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
Monday?
|
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
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
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,
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
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
|
|
Summary
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

|
|
Other Date
And Time Related Procedures are described on the following pages

|
|
Distributing Dates Over Intervals
|Holidays
|
Overtime Hours And Timesheets
VBA Procedures For Dates And Times
Worksheet Functions For Dates And Times
See the Dates And Times Topic Index For Information