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

Published: 23 Jan 2013
Last modified date: 03 Jan 2024

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 dialogue 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 dialogue, 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 dialogue 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.