End of Year Weeknumber Issues

Automations 1

Hi,

I'm having issues with many of my weeknumber formulas, now that it's the end of the year.

1. I have a Start Date column. I want the weeknumber of three weeks after the start date. Until now I used =WEEKNUMBER([Start date]@row) + 3. That doesn't work for the end of the year, because after 53 it goes to 54, 55, 56 ect.. Instead of going to 1, 2, 3
2. In another scenario I want to know if the Start Date is in a future week - not this week, but in a future week. Until now I've been using this formula: =IF(WEEKNUMBER([Start date]@row) > WEEKNUMBER(TODAY()), 1, 0). But again, this formula stopped working if the Start Date is in January '21 or past that.

Any solutions?? Do I need to change all my weeknumber formulas and just use date formulas?

Mike Wilday

You can always run an if statement to subtract 52 from the weeknumber if its over 52. And then it would revert once the date stops using the end of the year.

=IF(WEEKNUMBER([Start date]@row) + 3 > 52, Weeknumber([Start date]@row) + 3 - 52, Weeknumber([start date]@row + 3)

Does this formula work for you?

Leibel S

@Rivky Emert

For your first issue, change the formula to =WEEKNUMBER([[Start date]@row + 21)

your second issue is a bit more complicated because of how the weeknumber function behaves...

https://help.smartsheet.com/function/weeknumber