Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

4 Answers

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
    
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
add comment

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.