- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 10, 2024 05:56 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 10, 2024 06:49 PM
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!
Justin Ng
Programme Coordinator at Sistema Aotearoa
https://www.sistemaaotearoa.org.nz/
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 10, 2024 06:56 PM - edited Jul 10, 2024 07:41 PM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 11, 2024 09:10 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 11, 2024 08:21 PM - edited Jul 11, 2024 08:22 PM
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!
