DATETIME_FORMAT and WEEKNUM bringing back two different week #s when used on the same field

Hi all,
I have two formula columns, both pointing at one date field (“Date”). The formulas in the two fields are:

DATETIME_FORMAT(Date,‘W’)

and

WEEKNUM(Date)

For some reason, the week number in the WEEKNUM column is always 1 more than in the DATETIME column. So if the DATETIME column translates the week as 11, the WEEKNUM column translates it as 12 and so on.

Does anyone know why the week numbers are not lining up?

As stated for the documentation of Airtable’s formula functions, unless you explicitly tell WEEKNUM() to start counting weeks on Mondays, the function counts weeks by starting on Sunday. The format you chose for DATETIME_FORMAT() is explicitly saying “format the date using the ISO week”, and the ISO date format starts weeks on Monday.

DATETIME_FORMAT(Date, 'w') is equivalent to WEEKNUM(Date).
DATETIME_FORMAT(Date, 'W') is equivalent to WEEKNUM(Date, "Monday").

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