Skip to main content
Question

Timezone issues - one field is local, other is GMT

  • September 28, 2026
  • 3 replies
  • 57 views
CaitlenBerg
Forum|alt.badge.img+8

Overview: I have a base that tracks Email Build requests for our developers with users that work across multiple time zones. I’m looking to add reporting on lead time, comparing ‘Created Date & Time’ to ‘Due Date’.

Base Setup: Tickets are submitted via form, which captures Due Date on a calendar - this doesn’t ask for a time, so Airtable seems to default to covertly storing the time as the moment that selection was made, in GMT. All other times are currently set to show in the users’ own time zone, but I can standardize that to Pacific if needed. An automation populates the Created Date & Time of the ticket.

Need: I’m looking to report on the lead time, but the Due Date should not be counted as a workable day, so I would like that date to always default to 12:00 am (or 00:00:01). The output should be shown in Hours, not Days, as the SLA is 72 hours and that’s not being adhered to. 

I first worked through this with ChatGPT and I wound up creating a formula field called Due Date Midnight that is supposed to convert the date field:
SET_TIMEZONE( DATETIME_PARSE( DATETIME_FORMAT({Due date}, 'YYYY-MM-DD') & ' 00:00:01', 'YYYY-MM-DD HH:mm:ss' ), 'America/Los_Angeles' )

But for some reason, that’s showing all of the times as either 4:00pm PDT or 5:00 pm PDT.

I then created a second field to take the Due Date and convert it to just a date, calling it Due Date Fixed, and hopefully lose the time, using:

DATETIME_FORMAT({Due date}, 'YYYY-MM-DD')

And I’ve updated the formula above to use this field instead, but I'm still having the same issue. 

Any thoughts? I’m wondering if a script would work better, but I’ve never worked with those. Ultimately, I just need the number of hours between when the ticket was submitted and the first minute of the due date. 

    3 replies

    TheTimeSavingCo
    Forum|alt.badge.img+32

    Does this look right?  I think that once both fields are set to be the same time zone the hours should calculate as expected, and here’s the base I was experimenting with in case that’s helpful

     

    Time stuff is really fiddly though and I apologize if this is something you’ve already tried and doesn’t work.  If so, could you provide some example screenshots or perhaps an example base?

    ---

    I was also curious about the automation you mentioned that stamps in the created date and time.  I usually use ‘Default to Current Date’ but I’m guessing there’s some business logic I don’t understand?

     

    Adam, Airtable Consultant - https://thetimesaving.company
    Forum|alt.badge.img
    • DuaerTeam
    • New Participant
    • October 5, 2026

    The 4:00pm / 5:00pm PDT values are a strong clue: that is midnight GMT shown in Pacific time (UTC−7 or UTC−8). Airtable stores datetimes in GMT, and a date-only value still carries a time of midnight GMT.

    What should help:

    1. On Due date and Created Date & Time, open the field customization, turn on Use the same time zone for all collaborators, and set both fields to the same time zone (for example America/Los_Angeles, or GMT on both; the key is that they match).
    2. Wrapping a formula in SET_TIMEZONE(...) on its own won't change the result. Per Airtable's docs, SET_TIMEZONE only takes effect when it's used inside DATETIME_FORMAT.
    3. For the lead time in hours, once both fields are aligned:
    DATETIME_DIFF({Due Date Midnight}, {Created Date & Time}, 'hours')

    If Created is later than Due Date Midnight, the result is negative, so it's worth a quick filter on negative values while you validate.

    Disclosure: I'm on the Duaer team; this was drafted with AI help and checked against Airtable's support docs.

    anthonyXRay
    Forum|alt.badge.img+6
    • anthonyXRay
    • Participating Frequently
    • October 5, 2026

    What is likely going on here is that there is a mismatch taking place between how the timezone issues are being applied vs how they are being displayed.  The 4pm/5pm PDT are exactly midnight GMT in Pacific time depending on the season.  Try to set the Due Date Midnight field’s time zone to GMT and see if it shows “00:00:01”.  That way for the lead time you could then use,

    “DATETIME_DIFF({Due Date Midnight}, {Created Date & Time}, 'hours')”

     

    That will measure to midnight GMT.  If you need your SLA to count to midnight Pacific, the result will shift by 7 or 8 hours depending on daylight saving, so it is worth it to decide what to follow.  I don’t think you need a script yet, as this should be doable with formulas.

    Anthony at XRay
    Terms & ConditionsAccessibility statement