Changing WeekNum value to 52

I wanted the Weeknum of an end date field. The week number of those records that have 12/31/2021 is 1 instead of the last number of the year. How can I turn that into 52. I was trying to do a formula like this IF({End Date} = ‘12/31/2021’, “52” , WEEKNUM({End Date})) but it wasn’t working. I am new to formulas so if you can give me guidance I would appreciate it.

If {End Date} outputs a valid date, then its value will likely not be "12/31/2021" since dates are stored in UNIX timestamps. A more reliable solution would be something like IF(IS_SAME({End Date}, "12/31/2021", "day"), ...).

In any event, what you really should do is use DATETIME_FORMAT() to specify you want the ISO week of the year:

IF({End Date}, DATETIME_FORMAT({End Date}, "W"))

For reference, using a lower case "w" would output a 1, same as the WEEKNUM() function.

2 Likes

Thank you so much @Kamille_Parks!

In Airtable, weeks start on Sunday. I don’t know where you’re from, but in Europe it starts on Monday, so that to can be the reason why the week number is wrong. Is this is the case, you can also use:

WEEKNUM({end date}, "Monday")

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.