This article explains how to convert seconds to HH:MM:SS or DD: HH:MM:SS by using a calculated field using one of the following procedures in Tableau.
Steps to Reproduce:
Let’s use the sample Excel file which contains a column name –Seconds- with some valid values. Open a newly created excel through Tableau. Once the data is loaded, perform the following mentioned steps for getting the desired result.
1. Select the required Measure/Dimension say “Seconds” and add it to the row self.
2. Use the right-click menu (or drop down) from the field in the Data Pane of the left side-bar (under Dimensions) and look for Default Properties > Number Format > Custom>hh:mm:ss
3. Start the formula dialog box via the main menu using the Analysis/Created Calculated Field option or by right-clicking on a field.
Provide an appropriate Name to the calculated field say HH:MM:SS
Note: If you know your duration is always going to be less than 24 hours (86400 seconds), then you can take advantage of Tableau’s built-in date formatting. We can use the following calculation: DATETIME ([Seconds]/86400)
4. Use the newly created calculated value HH:MM:SS in the view.
Use the DATEPARSE function in a calculated field to convert a date string into a datetime data type. This is useful when you are working with date data that Tableau recognizes only as a string data type. The DATEPARSE function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extracts data sources. The DATEPARSE function takes two parameters, “format” and “string.”
1) If your seconds are as a string you can use:
DATEPARSE (“s”, [your seconds’ field])
2) If your seconds are an integer, you can use:
DATEPARSE (“s”, STR ([your second’s field]))
To change how the new datetime value displays in the view, right-click the DATEPARSE calculation in the Data window, and select Default Properties > Date Format.
Use the DATEADD function in a calculated field which allows you to specify a portion of a date and increase it.
DATEADD (‘second’, [Seconds], #00:00:00#)
You can also follow the Jonathan’s sir technique/calculation for converting your seconds into HH:MM: SS. It avoids doing any string manipulations. Here’s the one for days:
IIF ([Seconds] % 60 == 60, 0, [Seconds] % 60) + IIF (INT ([Seconds]/60) %60 == 60, 0, INT ([Seconds]/60) %60) * 100 + IIF (INT ([Seconds]/3600) % 24 == 0, 0, INT ([Seconds]/3600) % 24) * 10000 + INT ([Seconds]/86400) * 1000000
Read the below Knowledge base article
Leave a comment
You must be logged into post a comment.