DATETIME_DIFF is killing me...

[larae]

Problem formula: DATETIME_DIFF({Task Due}, TODAY(), 'hours')

Short Version:

It's currently 5pm Pacific. The Task Due is due tonight at 12:00am Pacific. DATETIME_DIFF reflects -24. Why is this hours calculation reflecting 24 hours instead of 7?

Long Version:

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.

Last week I built a formula to offset the GMT change by 8 hours:

IF({Deadline Day Calculation} <= "-17", "PAST DUE", IF({Deadline Day Calculation} >= "-17", IF({Deadline Day Calculation} < "7", "TODAY", IF({Deadline Day Calculation} >= "7", IF({Deadline Day Calculation} < "31", "TOMORROW", IF({Deadline Day Calculation} >= "31", UPPER({Day of Week}), ""))))))

It worked well for a week. Suddenly it's no longer working correctly because the DATETIME_DIFF formula is rounding up hours to the nearest day rather than reflecting actual hours as it did last week. I.e., reflecting 24 hours between 5pm and 12a instead of 7 hours.

I've tried using NOW() instead of TODAY() to no effect.

Please help. This is driving me nuts. Thank you!

[Sistema_Aotearo]

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!

[TheTimeSavingCo]

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!

[larae]

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!

[TheTimeSavingCo]

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!