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]