Help with DAys/Hour/Minute/Seconds

[RavensFtw]

hello!
I am having a hard time getting lets say 90000 Seconds to Day Hour Minute Seconds
Every time i put it in gives me a date (without obviously seconds), or it will only show 00:00:00:xx(number)

Any ideas?

[RavensFtw]

Or it will put a day of the week. I just want How many Days, how many hours, how many minutes, how many seconds.

[mehmetcik]

http://excelsemipro.com/2010/08/date...tion-in-excel/

Please Login or Register  to view this content.

format cell as time

Result = 01:00:00

because 86400 is subtracted from your 90000 and discarded, that is one day and therefore cannot be displayed.
the remaining 3600 is displayed as 01:00:00 ie One hour.

if your number is 90000 then int(90000/86400) will give you days.
mod(90000, 86400) will give you your time element.

[RavensFtw]

I figured it out but thanks. it can be displayed.. all you have to do is put DD:HH:MM:SS in custom ... and then in the equation just put 60*60*24 and it will come out.

[Tony Valko]

Divide by 86400 (number of seconds in a day) and format as d h:mm:ss.

This will only work up to 31days 23hrs 59mins 59secs.

[FDibbins]

See if this will do for you (it does not include the terms Hours, Minutes, Seconds in teh answer but does show the hrs, mins and secs...
=INT(A1/60/60/24)&" Days & "&TEXT(MOD(A1/60/60/24,1),"hh:mm:ss")&" Hours"
=1 Days & 01:01:06 Hours

[Vikas_Gautam]

Custom Formatting used.. (Inspired from Tony..)
You can use FORMAT PAINTER to format other cells as well..
check the attached file..