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:
[TABLE="width: 537"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Week Number (With formula)
[/TD]
[TD]Correct Week Number
[/TD]
[/TR]
[TR]
[TD]12/01/2014
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11/01/2014
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]10/01/2014
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]09/01/2014
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]08/01/2014
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]07/01/2014
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]06/01/2014
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]05/01/2014
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/01/2014
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]03/01/2014
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]03/01/2014
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]02/01/2014
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]01/01/2014
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

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