KNOWLEDGE BASE

## How to Calculate the Time Difference and Format Difference as "xxh xxm xxs" Between Two times That Are Formatted as String

Published: 22 Sep 2023

### Question

How to calculate the time difference between two times in a time range string, for example

"0900-0930" : the time difference should be 30minutes and be displayed as "0h 30m 0s"

### Environment

• Tableau Desktop

1. Create a calculation field [1 - StartTime (Str)] to get the start time part from the [TimeRange(Str)] field using SPLIT function.
SPLIT([TimeRange(Str)],"-",1)

2. Create a calculation field [2 -StartTIme(Datetime)] to convert [1 - StartTime (Str)] to Datetime type by using DATEPARSE function and add a dummy date(eg 2023-01-01) in front to enable DATEDIFF calculations in step.
DATEPARSE("yyyyMMdd-HHmmss","20230101-"+[1 - StartTime (Str)]+"00")

3. Create a calculation field [3 - EndTime（Str] to get the end time part from the [TimeRange(Str)] field using SPLIT function.
SPLIT([TimeRange(Str)],"-",2)

4. Create a calculation field [[4 -EndTime（Datetime）] ] to convert [3 - EndTime（Str] to Datetime type by using DATEPARSE function and add a dummy date(eg 2023-01-01) in front to enable DATEDIFF calculations in step.
DATEPARSE("yyyyMMdd-HHmmss","20230101-"+[3 - EndTime（Str）]+"00")

5. Create a calculation field [5 - TimeDifference(in seconds)] to calculate the time difference between start time and end time in seconds
DATEDIFF("second",[2 -StartTIme(Datetime)],[4 -EndTime（Datetime） ])

6. Create a calculation field [6 - Conversion (Hour part)] to convert the total seconds into XX hour and truncate the minutes and seconds.
INT(SUM([5 - TimeDifference(in seconds)])/3600)

7. Create a calculation field [6 - Conversion (Hour part)] to convert the total seconds into XX minutes ignoring the hours part and the seconds part.
INT(( SUM([5 - TimeDifference(in seconds)])-INT(SUM([5 - TimeDifference(in seconds)])/3600)*3600)/60)

8. Create a calculation field [8 - Conversion (Second Part)] to convert the total seconds into XX minutes ignoring the hours part and the seconds part.
SUM([5 - TimeDifference(in seconds)])-3600*[6 - Conversion (Hour part)]-60*[7 - Conversion (Minute Part)]

9. Create a calculation field [9 - Conversion (Final)] to format the date difference as "xxH xxm xxs" format
STR([6 - Conversion (Hour part)])+"H "+STR([7 - Conversion (Minute Part)])+"m "+STR([8 - Conversion (Second Part)])+"s"

Please see the attached workbook on the left side of thie article for more details.