converting seconds into time ?

[Former Member]

Hi I am converting seconds into time and I used the following formula:

=FormatNumber(Floor([Elapsed Time]/3600) ;"0") + ":" +

FormatNumber(Floor(Mod([Elapsed Time];3600)/60);"00") + ":" +

FormatNumber(Mod(Mod([Elapsed Time] ;3600) ;60) ;"00")

The problem I now have is it is giving time as

0:02:50

1350:07:59

::

0:11:15

5:05:35

359:55:49

::

0:01:33

6:32:38

::

0:04:20

0:25:53

3:38:58

72:00:04

::

0:02:32

0:16:19

3:41:01

How do I convert something like second row above 1350:07:59 as days which is 56. 

Please help.

Thanks,

[Former Member · Accepted solution]

Hi,

Can you try this:

=FormatNumber((Floor([Elapsed Time]/3600)) / 24 ;"0") + ":" + FormatNumber(Mod(Floor([Elapsed Time]/3600),24) ;"0") + ":"

FormatNumber(Floor(Mod([Elapsed Time];3600)/60);"00") + ":" +

FormatNumber(Mod(Mod([Elapsed Time] ;3600) ;60) ;"00")

I have not tested it so it might have some syntax or logical errors.

Regards,

Yuvraj

[Former Member · Accepted solution]

Thanks Yuvraj, I have used the below the formula:

=FormatNumber((Floor([Elapsed Time]/3600))/24 ;"0") + ":" +

FormatNumber(Floor(Mod([Elapsed Time];3600)/60);"00") + ":" +

FormatNumber(Mod(Mod([Elapsed Time] ;3600) ;60) ;"00")

and I got the below results.  How do I now convert it to a number such as 00.00, so that I can use it for calculation

0:02:50

56:07:59

::

0:11:15

0:05:35

15:55:49

::

0:01:33

0:32:38

::

0:04:20

0:25:53

0:38:58

3:00:04

::

0:02:32

0:16:19

0:41:01

3:00:04

[former_member565459]

Can you try this:

select

case when floor(130805.75/3600) < 10 then '0' || floor(130805.75/3600)

else '' || floor(130805.75/3600)

end

|| ':'||

case when mod(130805.75,3600) = 0 then '00'

when mod(130805.75,3600) > 0 and mod(130805.75,3600) < 600 then '0' || floor(mod(130805.75,3600)/60)

else ''||floor(mod(130805.75,3600)/60)

end

|| ':'||

case when mod(130805.75,3600) = 0 then '00'

when mod(130805.75,60) < 60 and mod(130805.75,60) < 10 then '0'|| floor(mod(130805.75,60))

when mod(130805.75,60) < 60 and mod(130805.75,60) >= 10 then ''|| floor(mod(130805.75,60))

end

from dummy;

[Former Member]

Hi ,

If your elapsed time is already in seconds then use the below formula to convert to days,

= seconds/86400

if your elapsed time is in hh:mm:ss then

(hh*3600)  + (mm*60) + (ss) = seconds

seconds/86400 = days

hope this will help you else provide more information

Regards,

Ragoth.C

[Former Member]

Hi,

to transform your calculated time into a Date use  ToDate function ex:

ToDate("0:02:35";"HH:mm:ss") will give you the date

1/1/1970 12:02:35 AM

Regards,

Rogerio

[Former Member]

Hi Rogerio,

Please see my second response to Yuvraj,

How do I convert the below  to a number, as currently it is showing as time.  I need a number so I can do a sum on it in other tabs

0:02:50

56:07:59

::

0:11:15

0:05:35

15:55:49

[Former Member]

Dear Chaz,

you already has a number, the number of seconds  you can use it to do the calculations and then convert back to a time, if you need so.

Regards,

Rogerio

[Former Member]

Hi Rogerio,

My user doesn't want in seconds.  He wants the seconds to be days for which I used

=FormatNumber((Floor([Elapsed Time]/3600))/24 ;"0") + ":" +

FormatNumber(Floor(Mod([Elapsed Time];3600)/60);"00") + ":" +

FormatNumber(Mod(Mod([Elapsed Time] ;3600) ;60) ;"00")

and I got below results:

0:02:50

56:07:59

::

0:11:15

0:05:35

15:55:49 Now I want to convert the above to number so they know it as Days: Hours and I can use the same for calculation

[amitrathi239]

Hi,

use this to display seconds in the days HH:MM:SS format.

=FormatNumber(Floor([Downtime]/86400);"00") + "Days " +

FormatNumber(Floor(Mod([Downtime] ;86400)/3600);"00") + ":" +

FormatNumber(Floor(Mod(Mod([Downtime] ;86400);3600)/60);"00") + ":" +

FormatNumber(Mod(Mod(Mod([Downtime] ;86400) ;3600) ;60);"00")

Amit

[Former Member]

Hi,

I would insist that the best approach is use the results in seconds , make your clculation and then use the result to convert back to days, hours, minutes and seconds.

Like you have the execution time of 2,000,000 seconds for one item and

1,000,000 to another.

Take the difference between these two numbers (1,000,000) and convert it to days , hours, minutes and seconds which gives you with the following result :

days	hours	minutes	seconds
11	13	46	40

Regards,

Rogerio

[Former Member]

Thanks Amit, Now do I convert the the hh:mm:ss to the below it is in excel.

My webi result with the formula you gave me is

0:11:15

0:05:35

15:55:49

I need my hh:mm:ss to be as below in excel

[Former Member]

I did as to what you said = seconds/86400 and my result is

I need my days bit to be just the way it is, but the decimal hours, I need it to be as below.  How do i do this

[amitrathi239]

HI

Use substr fuction to extract hour in new variable And then convert into the number format.after that divide this value by 24 to get the excel equilant values.

V hour=tonumber(substr(equip time;1;2))/24

Amit

[Former Member]

Hi Chaz,

Create a variable as

seconds= days* 86400 (eg: for 1.5 days    seconds= 1.5*86400=129600)

Then write formula as

=FormatNumber(Floor([seconds]/3600);"00") + ":" +                    (seconds/3600      )

FormatNumber(Floor(Mod([seconds] ;3600)/60);"00") + ":" +       ( mod(seconds,3600)/60 )

FormatNumber(mod(mod([seconds],3600),60);"00")                    (mod(mod(seconds,3600)/,60)

Regards,

Ragoth.C