ISO Week difference past year's end

user_16466266 0
Viewed 99

I need help with a report where I need to continue the week number beyond 52 (or 53). For instance, 30-Dec-20 is 53rd week. I used ISOWEEKNUM formula here. It is week number 53 until 3-Jan-2021.

Once it is 4-Jan-21, ISOWEEKNUM formula shows 1 as week number. Technically this is right. But for my report I need 4-Jan-21 should be numbered 54 and this continues till 10-Jan-21. Once the next date starts at 11-Jan-2021, I need the week number to show 55 and it continues.

Thanks in advance.

google-sheets-formula
1 Answers

Assuming you only care about dates after the year, we can take advantage of the fact that ISO weeks always start on a Monday. The trick is determining which Monday.

Inputs:

  • A1 : Start year, eg. 2020
  • A2 : Date in question, eg. 1/4/2021

Calculations:

  • B1 : This just precalculates 1-Jan of the input year.

    =DATE(A1,1,1)

  • B2 : Get the Monday of the week containing 1-Jan, or the next week, depending if 1-Jan has an ISO Week number of 1.

    =IF(ISOWEEKNUM(B1)=1,0,7)+B1-WEEKDAY(B1,2)+1

Result:

  • Take the difference in days and divide by 7 to get number of full weeks.
  • Adding 1 gives 1-index
  • INT is a shorter FLOOR for positive numbers.
=INT(1+DATEDIF(B2,A2,"D")/7)
user_42943990
Related