Microsoft Office Tutorials and References

In Depth Information

**Numbering Weeks**

Numbering Weeks

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

complicated.

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:

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+

WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)

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