how to convert 357646 seconds into hh:mm:ss format?

[Guest]

Does anyone know how to convert 357646 seconds into d/hh:mm:ss format.
ALso does anyone know how to find the different between date and time. e.g
different between 01/02/2005 10:50:14 and 02/02/2005 16:47:08?

 

[Fred Smith]

Times are stored as fractions of a day. To convert seconds to a time, divide
by the number of seconds in a day:

=357646/24/60/60, format as time/date

To get the difference between two dates/times, just subtract them:

=b1-a1, format as date/time

Excel doesn't handle negative times well, so make sure you subtract the
earlier from the later date.

 

[Ron Rosenfeld]

Excel doesn't handle negative times well, so make sure you subtract the
earlier from the later date.

To expand on your comment:

Excel, using the 1900 date system, does not DISPLAY negative times/dates.
However, the value in the cell remains correct (as in fractions of a day, be
they positive or negative) and can be used in other formulas; or formatted as
other than time or date.


--ron

 

[Fred Smith]

Now that's interesting. Very useful information. Thanks.

 

[Ron Rosenfeld]

Now that's interesting. Very useful information. Thanks.

You're welcome. I've used that "feature" in some workbooks of mine.


--ron

 

[Guest]

Is the answer 20:46:0 ? That is what I get.
How can you use date when you don't have a date in the question ?

 

[Fred Smith]

I understood the OP asked two separate questions:
(1) what is 357646 in date/time format, and
(2) how do you subtract 01/02/2005 10:50:14 from 02/02/2005 16:47:08

An Excel date field can contain both a date and a time. Date fields are the
number of days since January 1, 1900, and can contain fractions. The
fraction portion is the time of day (eg, .25 is 6:00 am because it's
one-quarter of the way through the day).