10/9/16

Tableau - Real Time Scenario 15: How to calculate time difference between In time and Out time in HH:MM: SS format?

Calculating time difference in Hours between two Date Time columns is easy. But Calculating Time Difference and showing that in HH: MM: SS format needs some logical knowledge.



In Tableau It's easy to get time difference in hours between two date times. Using below formula.

DATEDIFF('hour',[In Time],[Out Time])

Similarly if you want time difference in minutes then you can use below formula

DATEDIFF('minute',[In Time],[Out Time])

for time difference in seconds use below formula

DATEDIFF('second',[In Time],[Out Time])

Lets assume we are trying to find difference between 8/26/2015 9:30:26 Am and 8/26/2015 6:41:48 PM

Step 1:

Find the time difference in seconds use below formulas

Duration in seconds:

DATEDIFF('second',[In Time],[Out Time])

I got 33,082 seconds for given date times above.

Now we need to convert these seconds into HH: MM: SS format.

Step 2:

1 hour = 3600 seconds

33082/3600 = 9.189 hours. That means 9 hours and few more minutes is there.

Now take only hours part. That is 9.

Lets write calculation for this

[Duration in seconds]/3600

Above formula will give 9.189

But how to get 9?. Shall we use Round()?

If I use Round function, Suppose my calculation return hours as 9.551 then round will convert this to 10 which I don't want. Then which one is right function here?.

I Suggest Floor function which will give 9 what ever is there after decimal point.

Floor([Duration in seconds]/3600will give 9. But I want to show as HH format.

How to archive HH format when we have only single digit?

If your answer is concatenating “0” using logical statement “if” which will impact performance.

Then try this below formula

Hours:

Right(STR(Floor([Duration in seconds]/3600)),2) which will give 09

Step 3:

Now we need to write calculation for MM

33082/3600 = 9.189 hours now we have to take remainder of this. So that you will get the remaining minutes after converting the hours.

To get remainder in tableau we have to use “Modulo” operator (%)

Minutes:

Right(STR(Floor(([Duration In Seconds]%3600)/60)),2) which will give 11

Step 4:

finally we need seconds, left after converting HH:MM.

Seconds:

[Duration In Seconds]%60

Step 5:

Concatenate all these HH , MM and SS to show as HH:MM:SS

Duration:

[Hours] +':'+[Minutes]+':'[Seconds]

Google Q&A Forum