Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



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


Re: seconds to minutes:seconds

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


Re: seconds to minutes:seconds

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


Re: seconds to minutes: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





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.