Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

WEEKDAY() shows same day for 2 different dates

Topic Labels: Dates & Timezones
Solved
Jump to Solution
334 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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.