# Thread: Convert seconds to MM:SS (Excel97)

1. ## Convert seconds to MM:SS (Excel97)

I use the following format below in a cell to give me the AvgCallTotalInSeconds in minutes and seconds. The problem is that it gives me the seconds in 1/10th of a minute. How can I convert the AvgCallTotalInSeconds to MM/SS? Thanks for the help.

=BG34/60

2. ## Re: Convert seconds to MM:SS (Excel97)

I am sure there is an easier way to do this, however, you can do the following:

1. Use the following formula to get total minutes:
Quotient(BG34,60) This will drop off the decimal portion of the result.

2. Do the following to get seconds:
(BG34/60 - Quotient(BG34,60)) *60

If you do not have the Quotient function, go to Tools, Add Ins, and check Analysis Tool Pack.

3. ## Re: Convert seconds to MM:SS (Excel97)

If the value you are working is just an integer and not a time value the following formula will give a string version of what you want :<pre> =INT(BG34/60) & ":"&MOD(BG34,60)</pre>

If you require the answer as an actual time try :<pre> =TIMEVALUE("00:" & INT(BG34/60) & ":"&MOD(BG34,60)),</pre>

and format the cell containing the formual as mm:ss

Both the above should display 72 as 1:12, the former being text, the latter an actual time value.

Andrew C

4. ## Re: Convert seconds to MM:SS (Excel97)

Use this formula:

<pre>=BG34/86400
</pre>

That will convert the value in BG34 to an Excel Date/Time value (86400 is the number of seconds in one day). Then format the cell with the custom format:

[mm]:ss

With the brackets around the mm. That will display the time in minutes and seconds as elapsed time.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•