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

[Grace_Oestreich]

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?

[Kamille_Parks]

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

Airtable Support

Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, ...

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...