1

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?

4 Answers 4

1

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)

0
1

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.

0

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).

-1

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

enter image description here

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .