Week in, week out, Microsoft Excel

Week numbers with Microsoft Excel

© Claudia Hautumm / www.pixelio.deFor analyzing and visualizing data on a timeline we are often consolidating the data on a monthly basis. Especially for monitoring and reporting, however, you need a higher level of detail, i.e. you will have to analyze and visualize your data by weeks.

Unlike the months of the year, the definition of a week is not fully standardized. There are different approaches of how to calculate the numbers of the weeks. The results of the data analysis and the according visualizations depend on the week numbering method you are using.

This post describes 3 different methods of numbering weeks and shows their impact on the resulting data visualization.

The basics: 3 common methods of numbering weeks

Among others, there are 3 main concepts of calculating the number of a week:

The absolute week number starts counting the weeks on the 1st of January, i.e. week 1 is always from January, 1 to January 7, week 2 from January 8 to January 14, etc. The last week of the year (week 53) has only one or two days. Thus, every week starts on the weekday of January 1.

The first day of a week is either Sunday or Monday. The US week numbering starts counting on New Years Day, no matter what weekday the 1st of January is. Thus, the first week of a year can have anything between 1 and 7 days. In other words, the week (7 days) containing New Year’s Day is split into a truncated last week of the previous year and a truncated first week of the new year.

  • ISO 8601 Standard Week Number

The International Organization for Standardization issued a method for calculating week numbers called ISO 8601. ISO 8601 weeks start on Monday. The first week of year (number 1) is the one which contains minimum 4 days of the new year, i.e. the one which contains January 4. Every week has 7 days. Thus, the last few days of the previous year may be assigned to week 1 of the new year or – vice versa – the first few days of the new year may belong to week 52 or 53 of the previous year.

There are a couple of other approaches to calculate week numbers as well, but I think these 3 are the most common ones.

Determine the week number of any given date with Microsoft Excel

Here are the functions and formulas to determine the week number of any given date using Microsoft Excel (assuming the date in cell A1):

The following formula calculates the absolute week number:

=TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)

Microsoft Excel does not have a standard function to calculate the week number, but the Analysis ToolPak Add-In (coming with every installation of Microsoft Excel) provides the function WEEKNUM. WEEKNUM is based on the US week numbering approach:

=WEEKNUM(A1,1)

returns the week number of the date in A1 for a week starting on Sunday

=WEEKNUM(A1,2)

returns the week number of the date in A1 for a week starting on Monday

  • ISO 8601 Standard Week Number

You can use different formulas to determine the week number of a date according to the ISO 8601 standard. Here are 3 possible solutions:

  1. =ROUNDDOWN((A1-DATE(YEAR(A1+MOD(8-WEEKDAY(A1),7)-3),1,1)-3+MOD(WEEKDAY(DATE(YEAR(A1+MOD(8-WEEKDAY(A1),7)-3),1,1))+1,7))/7+1,0)
  2. =TRUNC((A1-DATE(YEAR(A1-MOD(A1-2,7)+3),1,MOD(A1-2,7)-9))/7)
  3. =1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)

Download an example workbook with different week number calculations here:

Download Week Number Calculations (Microsoft Excel 2003, 115.0K)

The workbook contains the formulas described above, additional formulas to calculate the Monday of a week and 3 ways of custom formatting to display week numbers. Furthermore there is an additional sheet showing the limitations of the methods with regards to early dates (January 1900). I will not discuss the early dates here, because I never had to deal with them in my professional work. The sheet is only for the sake of completeness and for the ones who are interested in the early dates as well.

Which method should be used?

At first sight it seems to be a matter of taste or simply a question of what you are used to. But there is more to it than meets the eye:

If you are doing data analysis and visualizations on a weekly basis, you have to care about how your audience will interpret the definition of a week and the according visualization. For sure, this will be different across the world, but I guess, a lot of people will intuitively associate the definition of a week with the following 2 assumptions:

  1. A week starts on Monday.
    Honestly, I never understood the concept of a week starting on any other weekday. I think we all agree that Sunday is the last day of the weekend, right?
  2. A week contains 7 days.

Especially the question of how many days a week contains has significant impact on visualizations on a weekly basis. If we are comparing data on a time axis, all periods (i.e. categories on the x-axis) should be of the same length. Agreed, the months of a year do not have the same number of days as well. But the variances are smaller (range from 28 to 31 days) and everyone is aware of the fact that February has fewer days than March. This is not the case for the somehow “artificial” concept of week numbers.

In the following part of this post we will see the impact of different week numbering methods on the visualization of the data.

Week numbering methods and their effects on data visualizations

I made up a workbook with dummy data for the period from December 2009 to January 2010, using the 3 week numbering methods described above, created simple column charts and compared the results:

Absolute Week Numbers

Using absolute week numbers results in the following visualization:

Absolute Week Numbers - click to enlarge

Week 53 of 2009 contains only one day (December 31, 2009), all other weeks of 2009 are from Thursday to Wednesday and all weeks of 2010 start on Friday.

To me this is totally unclear and confusing. Imagine the start and end dates of the weeks would not be displayed below the x-axis (i.e. you would only see the week numbers). You would probably have no idea that week 50 of 2009 is the period from December 10 to December 16 and that week 53 contains only one day.

US Week Numbering

Using the US week numbering with weeks starting on Monday (i.e. Excel’s WEEKNUM([date], 2)) leads to a completely different visualization of the same data:

Excel WEEKNUM function - click to enlarge

The sum of the values for the period from Monday, December 28, 2009 to Sunday, January 3, 2010 is split between week 53 of 2009 and week 1 of 2010.

I consider this an undesirable effect:

We want to compare our data on a weekly basis, don’t we? But this visualization is not comparing full weeks (i.e. 7 days), because one week is split into one column showing the sum of 4 days and one column showing the sum of 3 days.

Thus, the column chart does not visualize at a glance the performance in this 7-day-period compared to the other weeks. Either you are adding the numbers of week 53 and 1 and try to imagine how the chart would look like, if you would put the column of week 1 on top of the column of week 53. Or, even worse, you are not aware of the split and you are wondering why the performance in the “weeks” 53 and 1 is so poor compared to the other weeks.

ISO 8601 Standard Week Number

Using the ISO 8601 standard instead of Excel’s WEEKNUM-function, we get the following column chart:

ISO 8601 Week Numbers - click to enlarge

This looks clearer to me. Every week consists of 7 days and the sums of the values are comparable. Agreed, it is somehow weird that 3 days of 2010 seem to belong to 2009, but – in my humble opinion – compared to the other approaches this is the most intuitive way of visualizing the data on a weekly basis.

Download the example workbook for free:

Download Week Numbers with Excel (Microsoft Excel 2003, 98.0K)

The conclusion

The post showed that different methods of numbering weeks result in different visualizations of data on a weekly time line. Every method comes with its own advantages and disadvantages, but from my point of view the ISO 8601 week numbers are definitely the lesser of two evils. Therefore I recommend using this approach for analyzing and visualizing data on a weekly basis.

Update on Tuesday, April 14, 2009

Giedre and Nick pointed to an issue I have not been aware of (see comments below):

I created the download files using a German installation of Microsoft Excel. Excel automatically translates the built-in functions into the used language, but it obviously does not translate the functions of the Analysis ToolPak AddIn. In this case it did not translate the German function KALENDERWOCHE into the English WEEKNUM.

Nick was kind enough to send me corrected versions (saved with an English installation) and I updated the download files now. 

The funny thing I learned: Excel does not translate Analysis ToolPak functions from other languages than English, but it automatically translates these functions from English into other languages.

Anyway: the files should work now. Let me know if there are any other issues.

Many thanks to Giedre and Nick.

Add a Comment

Your email address will not be published. Required fields are marked *