Date difference between two dates in hours and Minutes

Question & Answer
,
1

Hello Team,

Could you please assist me in calculating the difference between two dates in hours and minutes? I have two date fields along with timestamps, as shown in the snapshot.

image

2

Hi @tdr_Dinesh,

You can use dateDiff to get the difference in minutes between the 2 dates/times

dateDiff({tpep_pickup_datetime},{tpep_dropoff_datetime},"MI")

and then you can use this formula in a calculated field to get the minutes converted to a string of HH:MM

concat(
ifelse({datediff_MI} / 60 < 10,concat('0',tostring(floor({datediff_MI} / 60))),tostring(floor({datediff_MI} / 60)))
,':',
ifelse(({datediff_MI} % 60) < 10,concat('0',tostring(floor(({datediff_MI} % 60)))),tostring(floor(({datediff_MI} % 60))))
)

Hope this helps!

3

Thank you @andres007

4

Hi @andres007,

Thank you, I would like to check is it possible to show in HH:MM: SS format. Please assist.

5

Sure, the approach is very similar.

To get the seconds between the date/times you can use this formula.

dateDiff({tpep_pickup_datetime},{tpep_dropoff_datetime},"SS")

And for converting the seconds to HH:MM:SS

concat(
ifelse({datediff_SS} / 3600 < 10,concat('0',tostring(floor({datediff_SS} / 3600))),tostring(floor({datediff_SS} / 3600)))
,':',
ifelse(({datediff_SS} % 3600)/60 < 10,concat('0',tostring(floor(({datediff_SS} % 3600)/60))),tostring(floor(({datediff_SS} % 3600)/60)))
,':',
ifelse({datediff_SS} % 60 < 10,concat('0',tostring(floor({datediff_SS} % 60))),tostring(floor({datediff_SS} % 60)))
)
6

Thank you @andres007