Microsoft Office Tutorials and References
In Depth Information
Everyone knows that a year consists of 52 weeks. So, for any given day, you should be able to
determine the week number, right? The question seems simple, but in real life, it’s a bit more
To calculate a week number, the WEEKNUM function seems like the ticket. This function accepts
a date and returns the week number. It also assumes that the week containing January 1 is the
first week of the year. Therefore, the first week of the year can consist of as few as one day (for
example, 2011) or as many as seven days (for example, 2006).
The WEEKNUM function doesn’t conform to the International Standards Organization (ISO)
standard. According to the ISO standard:
h A week always begins on a Monday and ends on a Sunday.
h Week 1 is the week that contains the year’s first Thursday.
This definition means that, in some years, the first days of the year might have the week number
52 or 53. (They’re in a week in the preceding calendar year.)
The Excel WEEKNUM function uses an optional second argument, which lets you
specify the weekday that’s the first day of the week. If this second argument is 2, weeks
are assumed to begin on Monday rather than on Tuesday. However, using this option
still doesn’t make the WEEKNUM function correspond to the ISO standard.
The following formula, which was created by Laurent Longre, returns the ISO week number for
the date in cell A1:
Figure 102-1 compares three ways to calculate the week number for the date in column A:
h Column B: Using the Excel WEEKNUM function (with Sunday as the week start day)
h Column C: Using the Excel WEEKNUM function (with Monday as the week start day)
h Column D: Using the previous formulas listed