DATETIME_DIFF challenges

[Spruce]

Hi All! I am trying to work with the DATETIME_DIFF formula but running into some issues. I am assuming the issues come from time/timezone complications, but not sure what the most elegant way around it is...

My goal: I am trying to calculate the difference in time between an expected completion date and an actual completion date.

The situation: Both date fields included time stamps driven by API connected apps and are set to the same time zone.

Date 1: Estimated Pickup Date (defaults to 12am)

Date 2: Ready for Pickup Timestamp (an exact time a checkbox was ticked, i.e. modified)

Formula I am currently trying: 

IF({Ready for Pickup Timestamp}, DATETIME_DIFF({Ready for Pickup Timestamp},{Estimated Pickup Date}, "days"))

 Incorrect results examples:

• Showing "0" days when Date 2 is at 2:41pm Oct 27th and Date 1 is Oct 28th but at 12am. Should show "-1 day"
• Showing "-1" days when date Date 2 is Oct 25th at 4:24pm and Date 1 is Oct 28th at 12am. I have no idea why this is happening... Should show "-3 days"

Any smart people out there have an idea what might be happening and how to fix it?

Thank you!
Devon

[Arthur_Tutt]

Hey @Spruce ! My bet is it's a rounding error. Keep in mind that the difference between Oct 27th at 2:41pm and Oct 28th at 12:00am is less than 1 full day (it's about 9 hrs 20 min), so 0 days is technically correct. 

You mentioned the time on estimated pickup date defaults to 12:00am, but are you free to set a new default? I tried using your calculation but changed time to 11:59pm and I got the results you were looking for. 

Alternatively you could just pre-emptively add + 1 to your calculation. 

[kuovonne]

DATETIME_DIFF() only returns whole days, not partial days. You may be better off calculating the number of hours difference, dividing by 24, and then rounding up.

The difference between Oct 27 at 2:41pm and Oct 28 midnight is less than 24 hours, so it is zero days.

I'm not sure why the difference between Oct 25 at 4:24pm and Oct 28 midnight is showing up as only one day. There might be something going on with conversions to GMT. (Airtable date/time fields are stored in GMT.) But I suspect there is more going on.

[Spruce]

Hi @Arthur_Tutt , yeah that's right. Although there is some other funky stuff going on too... Unfortunately I can't change the time on that first "estimated pickup date" since it is being generated by Fillout.

Adding the +1 to the calculation is smart and might be the move...

Thanks for weighing in!
Devon

[Spruce]

Hey @kuovonne , yeah I think you're on to something with all those thoughts. I had always heard from developers that date and time-zone stuff is some of the trickiest they deal with and now I see why!

Thank you for your support!
Devon