# Calculating the calendar week in Excel

I am using the following formula to calculate the calendar week if referenced to a cell with date:

``````=INT((A1-DATE(YEAR(A1);1;1)-WEEKDAY(A1))/7)+2
``````

but it does not work properly. If I enter `24.08.2012` I get calendar week 35 but it is actually 34.

Does anyone know what is going wrong?

-

For Excel 2007 `=WEEKNUM(A1,1)` and `=WEEKNUM(A1)` return `34` whereas `=WEEKNUM(A1,2)` returns `35`, when `A1` is 24.08.12 (see Ron de Bruin).

-

I assume the formula above only works with US dates, since the first calendar week always starts on the 1st of January. In Europe it's different (in VB terminology: first four days).

In VBA it can be done like this:

``````Format(datefield, "ww", vbMonday, vbFirstFourDays)
``````

Excel 2010 comes with a built-in function for the calendar week.

-

This formula will give you the ISO week number [where week 1 of each year starts on the first Monday on or after 29th December, and every week has 7 days]

`=INT((A1-WEEKDAY(A1;2)-DATE(YEAR(A1+4-WEEKDAY(A1;2));1;4))/7)+2`

as Andreas says in Excel 2010 you can get the same with WEEKNUM function using

`=WEEKNUM(A1;21)`

-
Got to deserve +1 if only for the consideration of using the OP's delimiter! Odd (though correct!) that we started this year in week 52 - no wonder time seems to fly by. –  pnuts Sep 26 '12 at 14:38

Your formula gave me an error with the semicolons when picking the date and the year. Instead has to use commas. Also, I don't know if you have specific formatting on A1, but 24.8.2012 is by default in Excel 2013 not a valid date. However, your formula works on a new Excel workbook after changing the date format and the commas.

`=INT((A1-DATE(YEAR(A1),1,1)-WEEKDAY(A1))/7)+2`

-
This happens if you use the german version. You have to replace "," with ";" otherwise you get an error. Not sure if this is the reason the formula fails. –  Stephen Younger Aug 24 '12 at 14:01
UDF works: rondebruin.nl/weeknumber.htm –  Stephen Younger Aug 24 '12 at 14:17