4 Replies Latest reply on Jan 4, 2019 12:13 PM by raunak jaiswal

# converting Average of seconds into dd:hh:mm:ss format in tableau

Hi all,

I am trying to convert average of time in seconds to dd:hh:mm:ss format. I am new to tableau, I tried using custom format, but it converts days like 1 day to 31 days. how to I eliminate this issue so as I get average of time in seconds > converted to DD: HH:mm:ss format.

• ###### 1. Re: converting Average of seconds into dd:hh:mm:ss format in tableau

There is no direct way to chnage this to required format. You need to calculate each of the values separately and concatenate them to form this format.

consider [seconds] is the fields that has your avg. seconds value

Usually

Days = floor([seconds]/86400)

seconds_1( remaining after Days calculation) = [seconds] - [Days]*86400

Hours = floor([seconds_1]/3600)

.

.

.

Floor will only take the full days/hours and leave the rest of the fractions to be converted to a lower scale of time.The same logic can be implemented in tableau and the results can be concatenated appropriately.

Hope this helps

Jeevan Krishna

• ###### 2. Re: converting Average of seconds into dd:hh:mm:ss format in tableau

Hi Sumeet,

First of all create a calculated field as per your requirement as stated in this Tableau Knowledge Base article
Displaying Elapsed Time as HH:MM:SS or DD:HH:MM:SS | Tableau Software

Then, convert the data type (it will either be a number or date by default) of the calculated field to String.

Cheers!

• ###### 3. Re: converting Average of seconds into dd:hh:mm:ss format in tableau

Hi Sumeet,

I helped someone a while ago on a similar request.  Please see this Community Forums thread (Calculating total in hh:mm:ss format  ).  The scope of the request changed from summing the days-hours-minutes-seconds to an average.  It may be helpful.  It would be best though if you can provide some mock data or your workbook (.twbx format).  Thx, Don

• ###### 4. Re: converting Average of seconds into dd:hh:mm:ss format in tableau

Hi Don Wise,

I have a similar kind of query which I posted on forum below is the link

I have attached the workbook as well. Can you please check as the solution you provided did not worked for me.

Thanks,

Raunak