Week number that overlaps between 2 years in excel

Question

I am trying to get week number in excel where my week starts from Wednesday to Tuesday. I used =WEEKkNUM(A1,13), where A1 is a cell that contains a date.

Challenge: When I use weeknum for 1st of JAN 2016, it shows that the week number is 1 which is wrong. 30th of DEC 2015 is the first week of 53. In other words, when weeknum reaches to first day of a year, it consider it the first week of the year which wrong. Attached screenshot of the issue.

Expectation: 1st-5th of JAN, 2016 must be part of week 53.2015 and not week 01.2016

Please help.

Thanks!

Answer 1

Correct formula is =ISOWEEKNUM(A2-2)

Answer 2

Before the advent of easier formulas to calculate ISO week numbers I had a longer method to do so. The advantage of that type of formula is that it's endlessly adaptable based on day start or date start.

This version should give you week number based on week 1 start date being the first Wednesday of the year:

=INT((A2-WEEKDAY(A2-3)-DATE(YEAR(A2+1-WEEKDAY(A2-3)),1,7))/7)+2

Using that formula I get the following years with 53 weeks:

2014, 2020, 2025, 2031

Answer 3

Another solution:

= MOD(WEEKNUM(A1,13)-IF(WEEKDAY(DATE(YEAR(A1),1,1),13)>1,1,0)-1,52)+1

Answer 4

In M2 enter this formula =WEEKNUM(A3,13)-1, then from M3 enter this formula =IF(WEEKNUM(A3,13)=1,M2,WEEKNUM(A3,13)-1) and drag it to the bottom. You will have 52 weeks instead of 53, which is logical since you do not have cross weeks.

2015 crossing.

2016 crossing.