Help

Re: WEEKDAY() shows same day for 2 different dates

Solved
Jump to Solution
908 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MatthieuB
4 - Data Explorer
4 - Data Explorer

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 :
Capture d’écran 2021-04-15 à 09.35.23

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

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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.

Screen Shot 2021-04-15 at 6.11.20 AM

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

Screen Shot 2021-04-15 at 6.14.28 AM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

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.

Screen Shot 2021-04-15 at 6.11.20 AM

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

Screen Shot 2021-04-15 at 6.14.28 AM

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.