Changing WeekNum value to 52

Topic Labels: Formulas
1318 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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.

3 Replies 3

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.

4 - Data Explorer
4 - Data Explorer

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")