converting a number into days:hours:minutes

ToffaJ

New Member
Joined
Sep 15, 2002
Messages
27
Heres a doozey...

Does anyone know how to convert a straight number into days:hours:minutes. So if you have a number that is say.. 1269859... which are seconds, how can you convert that into days and so on... what is the formula for the cell format???

Thanks in advance
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Divide by 86400 to get decimal days (60 *60 *24).

To get elapsed hours use the format [h]:mm:ss.

I do not know a format for elapsed days:hours:minutes, but this formula works:

=INT(A1/86400)&" "&TEXT(A1/86400-INT(A1/86400),"hh:mm:ss")

This returns a text string not a value, so you will not be able to use it in other calculations.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,437
Messages
5,831,625
Members
430,077
Latest member
CoulterM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top