Loading

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

Julkaisupäivä: Apr 25, 2025
Tehtävä
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.
Vaiheet
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. 
 

 
Lisäresurssit

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
 

Knowledge-artikkelin numero

001458125

Liitteet

time formatting_v2018.1.twbx

19 KB

 
Ladataan
Salesforce Help | Article