WEEKNUMBER formula: fix issue when 31th december is in mid week

Chemi Pérez

Good afternoon from Spain.

I faced an issue with WEEKNUMBER formula.

30th december is monday, 31th december is tuesday. If we calculate their weeknumber, the answer is '1'.

This behavior is incorrect. 30th and 31th december should be = 53 (week number).

I wanna ask to fix this. When one of the last days of december are is in the same week than 1st january, the week number shouldn't be 1. It must be 52 o 53 or whatever.

Thanks in advance,

Chemi

KPH

Smartsheet calculates week numbers based on Mondays. The first day of the year may not be in the first week of the year because of this. And similarly, the last few days of the year may be in the first week of the new year.

You can use an IF function to adjust the weeknumber. You could do something like:

=IF(AND(MONTH(Date@row) = 12, WEEKNUMBER(Date@row) = 1), WEEKNUMBER(Date@row - 7) + 1, WEEKNUMBER(Date@row))

This says that if the date is in December and the weeknumber is 1 then take the weeknumber from the date 7 days ago and add 1 to it. If this is not the situation then return the normal weeknumber.

So, if the 31st December is showing as weeknumber 1 it would calculate 31-7 (24), find the weeknumber from December 24 (52) then add 1 to it (53).

Hope that helps.