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]/3600) will 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]

Do you feel that you are Tableau Expert? Then try this challenge 7

Here I am coming with real time challenge. Those who are interested can try this and test your knowledge.

My challenges are not too tough to solve. Very simple and will take few minutes. Getting idea and logic to solve these challenges may feel you tough. After finding solution to my challenges people will feel something they learned or something they understood differently. Let's see now how you will think about this challenge.

Look at below image.

Here I am comparing Sum(Sales) for selected Sub Category vs Other Sub Category in South Region by each Year. Now the challenge is how you will do this in tableau.

Note: Please Download the workbook attached below in this post to solve this challenge

See below images for more examples.

Example 1:

Example 2:

To Solve this challenge please download below workbook:

For more challenges: Click Here