KNOWLEDGE BASE

Converting Seconds to HH:MM:SS or DD:HH:MM:SS


Published: 23 Jan 2013
Last Modified Date: 02 Mar 2017

Question

How to convert fields that contain only seconds data 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

To convert seconds to a string displaying HH:MM:SS or DD:HH:MM:SS, create a calculated field using one of the following procedures:

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:
 
STR(INT(SUM([<fieldname>])/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. 
 

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?