Help

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

Topic Labels: Dates & Timezones
1851 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Grace_Oestreich
4 - Data Explorer
4 - Data Explorer

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?

1 Reply 1

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