# WEEKNUM Formula Calculating Incorrectly

#### JayReddy

##### New Member
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to MrExcel.

Try:

=WEEKNUM(A2,2)

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)

Pls ignore,I was looking at the wrong column

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.
1+INT((A2-DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,5)+WEEKDAY(DATE(YEAR(A2+4-WEEKDAY(A2+6)),1,3)))/7)

Great - thanks a lot for your help Andrew!

Formula: =WEEKNUM(A2,2) worked perfectly

Replies
1
Views
321
Replies
3
Views
228
Replies
30
Views
768
Replies
3
Views
303
Replies
2
Views
215

1,196,048
Messages
6,013,096
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back