Changing WeekNum value to 52

[Yaakov_Zehnwirt]

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.

[Kamille_Parks]

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.

Airtable Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the format of DATETIME_FORMAT(Datetime, 'format sp...

[Yaakov_Zehnwirt]

Thank you so much @Kamille_Parks!

[Databaser]

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