Jan
1
2016

How to convert “Seconds” into “HH:MM:SS”

2This 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.

http://msyvonnelin.com/tag/nikon-f3 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.

METHOD 1

1. Select the required Measure/Dimension say “Seconds” and add it to the row self.

1

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 buy Lyrica Pregabalin Default Properties > Number Format > Custom>hh:mm:ss

2

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

read the article 2 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)

3

4. Use the newly created calculated value HH:MM:SS in the view.

4

METHOD 2

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])

5

2) If your seconds are an integer, you can use:

   DATEPARSE (“s”, STR ([your second’s field]))

6

7

2tipsTo 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.

 8

METHOD 3

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#)

9

10

METHOD 4

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

METHOD 5

Read the below Knowledge base article

http://kb.tableau.com/articles/howto/converting-seconds-to-hh-mm-ss-or-dd-hh-mm-ss

 

 

 

 

 

Related Posts

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.