DATETIME_DIFF returning strange results

My formula for DATETIME_DIFF is not returning expected results but I can’t figure out what it’s calculating.

The first should return a difference of 1:48, the second of 0:20.

When I set the function without any specifiers, I get 4:12 and 5:40, and when I specify minutes, I get 0:04 and 0:06.

Any help would be appreciated.

Screen Shot 2020-10-13 at 10.53.57 AM
Screen Shot 2020-10-13 at 10.59.24 AM
Screen Shot 2020-10-13 at 10.54.12 AM Screen Shot 2020-10-13 at 10.54.21 AM Screen Shot 2020-10-13 at 10.54.38 AM

Try using seconds instead of minutes. Formula should read

DATETIME_DIFF({End Time},{Start Time},"seconds")

image

2 Likes

Nope … it’s giving me the same as with no specifiers:

Screen Shot 2020-10-13 at 12.23.11 PM Screen Shot 2020-10-13 at 12.23.20 PM

The numbers are clearly related (i.e. 4:12 somehow relates to 1:48 and 5:40 relates to 20) but even the differences between my result and what I am expecting aren’t consistent (2:30 and 4:20).

You need to put “End Time” first in your formula and “Start Time” second.

Also, make sure that both of your date/time fields have the same time zone setting.

No - that is still giving me weird numbers. Also, look what happens when I set the start and end times the same - I end up with a 6:00 baseline … when obsv should be zero. Something is weird - a setting somewhere?

Oh! you were on track with the time zone setting … I can get the correct calculation when I turn off the “use same time zone for all users” - but the weird part is, it only works if I turn it off for just one of the start/end times - if I do it for both, then we are back to the same result.

But I guess this will work for now … !

As I previously said in my post above (and as @Nathalie_Collins showed you in her post above), your formula is wrong.

You need to put the End Time first, but you currently have the End Time second.

Yup, I swapped them, thanks!

1 Like