Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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)

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer
    
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

You must log in to answer this question.

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