Weeknummers in Excel

Introduction

When you want to get the weeknumber from any given date, there is an Excel add-in package that provides the =WEEKNUM() function. Just go to [Tools]->[Addin] and select "Analysis Toolpack".

However, this function calculates weeknumbers according to the American standard. Which means that 1/1 always is the first day of week 1 and that week 2 starts on the first sunday or monday (depending on where you've defined your start of the week) after that. There are drawbacks here! The first week almost never has 7 days, while there are also a lot of incomplete weeks 53's or even 54's... Thus making comparison of weekresults unreliable.

Anyway I'm used to the European way to calculate weeknumbers (also known as "the ISO Week standard" ~ ISO8601:2000). In this definition thursdays play the key part. It says "all weeks begin on monday, week 1 starts on monday of the first week of the calendar year with a thursday"...

I like my weeks to start on a sunday though and furthermore I want the output to be like <YYYY><WW>. Because there is no function available in Excel that gives me the right results, I made it myself. The formula is a little complex, I'll try to explain how I came up with it. Here it goes.

Formula

When calculating the weeknumber for any given date, you always have to use the thursday in the week of that given date. Excel has a useful function for that: =WEEKDAY(). This function returns 1..7 for Sunday..Saturday (Hey! Weeks start on sunday by default). So, if my date is in A1, then this formula will allways give the thursday in the same week "THX":

"THX" = A1 - WEEKDAY(A1) + 5

This thursday becomes the actual date for which we want to calculate the desired <YYYY><WW>; output. We want to know in what year it falls to find the <YYYY> part. So our formula will be like:

= (100 * YEAR(A1 - WEEKDAY(A1) + 5)) + &lt;WW&gt;

Now we have to look into the <WW> part. All we have to do is find the first thursday "TH1" of the corresponding year, calculate the number of days between that thursday and the thursday "THX" we've initially calculated and divide this by 7: ("THX" - "TH1") / 7. Giving us the number of weeks between both thursdays.

How do we find the first thurday? Well, we try to find a date that is always in week 1 and then calculate the corresponding thursday. For this date we can't take 1/1 or 1/2 (january 1st and 2nd) because they might fall into the last week of the previous year. This happens when 12/31 (december 31st) is a thursday. Instead we take 1/3 (january 3rd) of which we know for sure it won't fall in the last week of the previous year and calculate the thursday "TH1" that belongs to it:

"TH1" = DATE(&lt;YYYY&gt;1;3) - WEEKDAY(DATE((&lt;YYYY&gt;1;3)) + 5

With this we can calculate the &lt;WW&gt; part as follows:

<WW> = 1 + (("THX" - "TH1") / 7)

Substituting all referenced formulas gives us:

<WW> = 1 + (((A1 - WEEKDAY(A1) + 5)- (DATE(YEAR(A1 - WEEKDAY(A1) + 5);1;3) - WEEKDAY(DATE((YEAR(A1 - WEEKDAY(A1) + 5);1;3)) + 5))/7)

Putting it all together:

= (100 * YEAR(A1 - WEEKDAY(A1) + 5)) + (1 + INTEGER(((A1 - WEEKDAY(A1) + 5)- (DATE(YEAR(A1 - WEEKDAY(A1) + 5);1;3) - WEEKDAY(DATE(YEAR(A1 - WEEKDAY(A1) + 5);1;3)) + 5))/7))

The dutch equivalent:

= (100 * JAAR(A1 - WEEKDAG(A1) + 5)) + (1 + INTEGER(((A1 - WEEKDAG(A1) + 5)- (DATUM(JAAR(A1 - WEEKDAG(A1) + 5);1;3) - WEEKDAG(DATUM(JAAR(A1 - WEEKDAG(A1) + 5);1;3)) + 5))/7))

Complex but working...