How do you format the "number" in Microsoft Excel to Minutes and Seconds?

Question

What I want to do is have the cell(s) display ex. "1:30", meaning 1 minute and thirty seconds. I don't want it to display "0:01:30" for the same.
Also I will need to average the cells in a seperate cell, I am hoping I can use the AVG function, but with the format being in Minutes and Seconds screw it up?
Microsofts helpsite is virutually vacant of coherant info on this and no such available format is on Office2007.

tried the mm:ss, didn't work, date and time format just put "Jan-10 2009"

Answer 1

To add up minutes and seconds, you must include a leading "0:" in your data.
For example,
cell A1 enter 0:01:03 to indicate 1 minute, 3 seconds
cell A2 enter 0:02:04 to indicate 2 minutes, 4 seconds
cell A3 enter 0:05:05 to indicate 5 minutes, 5 seconds

When you sum these times, Excel will display the sum in "time-of-day" format, meaning that adding cell A1+A2+A3 will yield 1:11:10.
To prevent Excel form "rolling over" at the hour, you must enclose the minutes parameter of the format in brackets, format the cell as [m]:ss which will cause it to display 71:10 rather than 1:11:10

For above sample data, the Average will have result 23 minutes, 43 seconds
[m]:ss 23:43
[h]:mm 0:23
[h]:mm:ss 0:23:43

{=AVERAGE(IF(A1:A3<>0,A1:A3))}

This formula using an array formula, press Ctrl+Shift+Enter, rather than just Enter. Excel will automatically add the braces, and also each time we edit the formula later.

Best of luck.

Answer 2

MM:SS that should do the trick. You will need to this in as a custom format. I think the AVG function will work just fine.

Answer 3

Right click the cell with the time entry then select Format Cells. With the number tab selected, choose Time under the Category list and then your desired format under Type:.


For the average time cell enter =Average(first cell:last cell)
i.e., with times enterd in A1-A4 type, in cell A5,
=Average(A1:A4)

Answer 4

Use a custom format m:ss

Average function will work to give you the average time.

Answer 5

try this: precise justify the column: this is going to print out merely non-0 values functionality hms(N As long)    If N = 0 Then       hms = ""    Else       hms = hms(N 60) & ":" & precise("0" & (N Mod 60), 2)    end If end functionality functionality HT(N As long)    strS = hms(N)    HT = precise(strS, Len(strS) - a million) end functionality in the objective cellular, form =HT(A1) if A1 contains your first time extensive style

Answer 6

Simple

select your cells,

Select format and cells..


Select time and then your type... It's in the list..