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"
Last Modified Date: 25 Sep 2023
Environment
- Tableau Desktop
Answer
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.
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials