WEEKNUM Formula Calculating Incorrectly

[JayReddy]

Hi,

Looking for some help please ...

I'm trying to calculate week numbers for specfic dates (DD/MM/YYYY format) and the WEEKNUM calculation is not returning accurate results. I am trying to calculate the week numbers based on 1st Jan 2014 being in Week 01.

The formula that I am using is as follows:

=WEEKNUM(A2)

The week number returned for some dates is correct, however, for a number of records this is being returned incorrectly as per the below:

Date	Week Number (With formula)	Correct Week Number
12/01/2014	3	2
11/01/2014	2	2
10/01/2014	2	2
09/01/2014	2	2
08/01/2014	2	2
07/01/2014	2	2
06/01/2014	2	2
05/01/2014	2	1
04/01/2014	1	1
03/01/2014	1	1
03/01/2014	1	1
02/01/2014	1	1
01/01/2014	1	1

<tbody>
</tbody>

Can anyone help and suggest how this can be corrected please? ...

Thanks in advance for any hints / suggestions.

Regards,

Jay

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=WEEKNUM(A2,2)

 

[NeonRedSharpie]

I'm assuming it has to do with the calendar. For example:

January 12th, 2014 is a Sunday. It's the third Sunday on the calendar page. December 29th is the "first Sunday".

Try weeknum(A2, 2)

 

[Special-K99]

Pls ignore,I was looking at the wrong column

 

[Vidar]

Hi

Weeknumber is calculated differently around the world. Not sure which country you come from and which rule
Your country uses to deside when week 1 starts in a year. This link explains the differences.
In Norway we uses ISO week number, and Excel doesn't have a function that will give you the correct weeknumber historically.
I use this formula instead. If Your date is in 2:
1+INT((A2-DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,5)+WEEKDAY(DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,3)))/7)

 

[JayReddy]

Great - thanks a lot for your help Andrew!

Formula: =WEEKNUM(A2,2) worked perfectly