KNOWLEDGE BASE

Time Values In Hh:mm:ss Format Is Not Recognized Properly When It's More Than 24 Hours


Published: 17 Jul 2020
Last Modified Date: 20 Jul 2020

Question

How to read column values having time expressed in hh:mm:ss format. Example values: 27:22:55, 05:03:00 etc

Environment

  • Tableau Desktop

Answer

1. Read the values as a String as Date/Time type for such a column will append a date before the time values in columns
2. If you want to calculate average/sum of the duration, first convert the values like 27:22:55 into duration in minutes/hours/seconds using a calculation like below that gives duration in minutes

INT(( SPLIT( [Duration], ":", 1 ) ))*60
+
INT(TRIM( SPLIT( [Duration], ":", 2 ) )) 
+
INT(TRIM( SPLIT( [Duration], ":", 3 ) ))/60

3. Use this as a dimension and Table calculations like Window_average and Window_sum.

Example to calculate the average of duration: 

if 
FIRST()==0 then
WINDOW_AVG(ATTR([Duration in minutes]))
end

User-added image


 
Did this article resolve the issue?