WEEKDAY() shows same day for 2 different dates

[MatthieuB]

Hello,

I’m using WEEKDAY() to show me which day of the week (Jour (#)) an order came through.
For some reason, the formula shows me this :

Orders with different dates 15/04 and 14/04 shouldn’t show the same Day Number in Jour (#) which is where WEEKDAY() is applied.

I know it’s related to the fact that these orders came between 00:00 and 02:00 am, tho I don’t know how to resolve this.

Any idea ?

Thanks

[Justin_Barrett · Accepted solution]

Welcome to the community, @MatthieuB! :grinning_face_with_big_eyes: Airtable stores dates internally relative to GMT. Even if you set a date field to not display using GMT, the GMT data is still what’s used for calculations. If you don’t live in the GMT timezone, the actual change from one day to another won’t match your local time. For me—PDT in the US, which is currently GMT-8—the shift happens late in the afternoon.

To get an accurate representation, you’ll need to use DATEADD() to manually shift the time based on your local offset from GMT before doing the WEEKDAY() calculation. For me, the formula looks like this:

WEEKDAY(DATEADD(date, -8, "hours"))

[kuovonne]

Another option is to use the SET_TIMEZONE function inside of DATETIME_FORMAT

DATETIME_FORMAT(
  SET_TIMEZONE({Created at}, 'Australia/Sydney'), 
  'e'
)

If you want the day of the week as a number, you will need to wrap the formula in VALUE. On the other hand, if you want the day of the week in words, you can use a different format specifier, such as 'dddd'.

One advantage of this method is that you do not have to worry if your offset from GMT sometimes changes due to daylight savings time.

All of these functions are documented in the formula field reference.