seconds to minutes:seconds

Posted by jason on October 12, 1999 9:20 AM

i have a spreadsheet that i want to enter 10 seperate cells (E2:N2) of times in measured in seconds. i would like an excel formula that would sum these times and convert the sum to minutes:seconds. all the formulas and formatting i have tried don't seem to work. i would think this is a simple thing to do, but i haven't found it yet.

thanks,

Jason

Posted by Chris on October 12, 1999 10:20 AM

Jason,

The following formula should give you what you need:

=INT(SUM(E2:N2)/60)&":"&INT(MOD(SUM(E2:N2),60))

Chris

Posted by judi on October 12, 1999 10:37 AM

If your seconds are entered in as numbers (like 56 for 56 seconds) then try this:

=TRUNC(SUM(E2:N2)/60)+(SUM(E2:N2)-(TRUNC(SUM(E2:N2)/60)*60))/100

This will give you answer like 8.31 meaning 8 minutes and 31 seconds

Posted by Ivan Moala on October 12, 1999 3:36 PM

Jason,
Judi's & Chris's work well.
An alternative to maintain your time data integrity ie. if you change the format
would be to use the following formula

=YourCell/(60*60*24)

The division by 60 * 60 * 24 is neccessary
because Excel stores all dates as integers and all times as decimal fractions. Excel takes your value as been a single day or 24 Hour
period eg enter 2 = 2days = 2 24hr periods =2*60*60*24 secs
Times are stored as decimal numbers between .0 and .99999,
where .0 is 00:00:00 and .99999 is 23:59:59

and then format your cells like this;

In the "Format cells" Dialog
Select Custom
In the "Type:" box
Type in "mm:ss"

This should give you the results as Minutes Seconds
Use the format painter to copy the custom formats to
your data range.

Ivan