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.