Week number that overlaps between 2 years in excel

user_4135252 0
Viewed 7005

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!

enter image description here

excel
4 Answers

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. enter image description here

2016 crossing.

enter image description here

user_82596470

Correct formula is =ISOWEEKNUM(A2-2)

user_41352520

Another solution:

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

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

user_11242870
Related