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.
Changing WeekNum value to 52
+27If {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.
+25Thank 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")
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.