I have a field, DEADLINE DAY CALCULATION, that I want to reflect TODAY if Task Due is today, TOMORROW if Task Due is tomorrow, DAY OF WEEK if Task Due is after Tomorrow, and PAST DUE if Task Due is before today. Because Airtable functions in GMT and our team is in Pacific Time, using 'days' would change at 5pm our time to reflect that TODAY is PAST DUE. I cannot fix this by toggling on Time Zones in the Task Due field because we do not use times for due dates, and this toggle only works if times are not hidden.
I have a field, DEADLINE DAY CALCULATION, that I want to reflect TODAY if Task Due is today, TOMORROW if Task Due is tomorrow, DAY OF WEEK if Task Due is after Tomorrow, and PAST DUE if Task Due is before today. Because Airtable functions in GMT and our team is in Pacific Time, using 'days' would change at 5pm our time to reflect that TODAY is PAST DUE. I cannot fix this by toggling on Time Zones in the Task Due field because we do not use times for due dates, and this toggle only works if times are not hidden.
Yeah, I agree the timezone thing can be confusing! I'm also in pacific time and have to deal with this a lot!
If you haven't yet, check out this article on setting timezones with formulas:
https://support.airtable.com/docs/timezones-and-locales
Make a formula something like:
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
{DEADLINE DAY CALCULATION}, 'Pacific/Auckland'
),
'M/D/Y h:mm A'),
'M/D/YYYY h:mm A'
)
----
Additionally, here's some of the hiccups I encountered myself when I initially set up my base.
I was confused why the timeline view was stuck to GMT when I had set everything to NZST. Turns out I actually hadn't! because there were so many formulas interconnecting, I missed just ONE to set to NZST. Which for some reason changed a whole lot of everything!
Hmm, perhaps try using SET_TIMEZONE instead of offsetting it manually? https://support.airtable.com/docs/timezones-and-locales
Edit: Oops, sorry, @Sistema_Aotearo! I opened the tab and left the computer for awhile, didn't realize you had already said this!
Thank you both! Unfortunately SET_TIMEZONE hasn't worked because it just displays things differently, but doesn't calculate the actual hours any differently.
The problem at this point is that DATETIME_DIFF is rounding up to days rather than hours (displaying 24 hours all day, etc.). Any thoughts on this?
Appreciate it!
Thank you both! Unfortunately SET_TIMEZONE hasn't worked because it just displays things differently, but doesn't calculate the actual hours any differently.
The problem at this point is that DATETIME_DIFF is rounding up to days rather than hours (displaying 24 hours all day, etc.). Any thoughts on this?
Appreciate it!
Hmm, could you provide an example base with some deadlines, starting times, a formula field with this problem and use a number field to provide what you want the expected output to be?
Could you also set up the "Deadline" field to be exactly the same as your current setup, e.g. which timezone, is it set to display the timezone, is it set to use the same timezone for all collaborators etc?
Been trying to replicate this on my end but wasn't able to so I figure there's something different in our setups!
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.