KNOWLEDGE BASE

Displaying Elapsed Time as HH:MM:SS or DD:HH:MM:SS


Published: 23 Jan 2013
Last Modified Date: 25 Jul 2018

Question

How to display fields that contain only elapsed time as number data, such as number of days or number of seconds, into one of the following formats:
  • hh:mm:ss
  • dd:hh:mm:ss
Note: "hh:mm:ss" represents hour, minute, and seconds. "dd:hh:mm:ss" represents day, hour, minute, and seconds.

Environment

Tableau Desktop

Answer

For elapsed time values under 1 day (86400 seconds), then a field containing number of days can be formatted to hh:mm:ss. For values equal to or over 1 day, a calculation can be created to manually generate the desired label as a string.

For hh:mm:ss

1. Go to Analysis > Create Calculated Field.
2. In the Calculated Field dialog box, do the following:
 
1. In the Name field, type the following: hh:mm:ss
2. In the Formula field, type the following and then click OK:
 
SUM( [# of Seconds) / 86400
 
Note: The above formula is to convert a field containing number of seconds into number of days. If the field containing the elapsed time data is in a different measurement, such as number of minutes, the above calculation will need to be modified appropiately. The final result should be elapsed time as number of days.
3. Right-click [hh:mm:ss] in the data pane and select Default Properties > Number Format...
4. In the Default Number Format dialog, do the following and click OK:
1. Select Custom
2. In the Format: text box, type the following: hh:mm:ss
5. Drag [hh:mm:ss] into the view
 

For dd:hh:mm:ss

1. Go to Analysis > Create Calculated Field.
2. In the Calculated Field dialog box, do the following:
 
1. In the Name field, type the following: dd:hh:mm:ss
2. In the Formula field, type the following and then click OK
 
STR(INT(SUM([<fieldname>])/86400)) 
 
+ ":" + 
 
IF (INT(SUM([fieldname])%86400/3600)) 
< 10 THEN "0" ELSE "" END + STR(INT(SUM([fieldname])%86400/3600))
 
+ ":" + 
 
IF INT(SUM([<fieldname>])%3600/60) 
< 10 THEN "0" ELSE "" END + STR(INT(SUM([<fieldname>])%3600/60)) 
 
 
+ ":" + 
 
IF INT(SUM([<fieldname>]) %3600 %60) 
< 10 THEN "0" ELSE "" END + STR(INT(SUM([<fieldname>]) %3600 %60))

Note: Replace <fieldname> with the name of the field in your data source that contains the "seconds" data. 

 
Did this article resolve the issue?