# 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.