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

Note: the original [# of Seconds] should be used in calculations and to graph the value in the view, the [dd:hh:mm:ss] field should only be used as a label.

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([# of Seconds])/86400))

+ ":" +

IF (INT(SUM([# of Seconds])%86400/3600))
< 10 THEN "0" ELSE "" END + STR(INT(SUM([# of Seconds])%86400/3600))

+ ":" +

IF INT(SUM([# of Seconds])%3600/60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([# of Seconds])%3600/60))

+ ":" +

IF INT(SUM([# of Seconds]) %3600 %60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([# of Seconds]) %3600 %60))

Note: Replace [# of Seconds] with the name of the field in your data source that contains the "seconds" data.

Additional Information

If [# of Seconds] is an aggregated field (aka it contains an aggregation like SUM() or AVG()) then remove SUM() from the above calculations. SUM() can also be replaced with another aggregation like AVG().

To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Idea: Format number as time duration