How can you format cells in Excel to hold a minute:second format? All the forms of time formatting I have found have actual times in the cell, i.e. I can get it to read 15:45 (for 15 minutes and 45 seconds) but the actual value of the cell is 12:15:45 AM. Don’t want that, I just want to be able to be able to enter times in a minute:second format and be able to average the figures.
I’ve tried the help features and that damn paperclip knows nothing.
Which version of Excel are you using? In Excel 2002 (part of Office XP), at least, one of the standard time formats, available when you do a Format/Cells, is minute:second. And when you sum them, or average them, it works just fine, as long as the cell into which you’re putting the result is formatted the same way (if you don’t, you start getting raw values).
Oh, and everyone knows that the paperclip is a moron. You need to use Rocky, the little doggie!
With office 2000 it’s easy.
Just highlight the cells you’re gonna use. Right-click : Format Cells: Number Tab : Custom. Then Define a new type of number called “mm:ss”. This will give you the ability to enter minutes and seconds into your cells. However, for some stupid reason, you need to still enter your times as “00:15:45” for 15 mins 45 secs.
Anyhoo, as long as the cell you want the average in is also formatted to “mm:ss” then just type “=average(A1:A2)” in the cell you want the average to appear in. (If you want the average of A1 & A2, abviously).
Hope this helps.
PS - it is true : The paperclip sucks ass.
You’re making it sound difficult.
You don’t need a custom definition. Right-click : Format Cells: Number Tab : Time
The first entry in the type list should read 13:30, and you only need to enter the minutes and seconds exactly as displayed
Russell
How about decimals of seconds?
(Somebody asked me how to do that for a swim team, but my reputation as an Excel wizard exceeded my talents)
Works for entered values but not for calculated ones; for example:
Suppose you want to calculate three-quarters of thirteen minutes?
13*0.75 = 9.75 (9 and three quarters)
Format the result column as time and it turns into 18:00 (sox o’clock in the evening)
The reason for this is that date/time values, regardless of how they are displayed, are stored as a number/fraction of days, so 9.75 is .75 of the way through the tenth day, or six o’clock in the evening.
The way to get it to work properly is to divide your result by 24 and then format it as time, this way, you get 13*0.75 = 9:45
I didn’t think that sounded difficult.
Apologies to all.
Actually, it works OK if you enter thirteen minutes as 00:13. The result, as long as it’s in a cell that’s formatted as hours: minutes, shows 0:09. What it doesn’t handle well, obviously, is rounding! That exercise is left to the student.
(And I misspoke in my first post - the format is not minutes:seconds, but rather hours:minutes.)
I think you misunderstand; if you have a calculation that produces a result expressed in hours, formatting **the result[/]b] as time will make it look wildly wrong, as I explained above (for values entered into the formatted cell, it works fine, but for values placed there by a formula, it does not). Because times/dates are stored internally as days, Excel misinterprets the result of your calculation (suppose it is 1.5 hours) as being expressed in days, so Excel thinks you are giving it 1.5 days and will format it accordingly, which will result in the wrong value on the screen.
If you want to do averages on minutes and seconds of a time format, I don’t think you can just format the cell to get what you want. However, you can do a format to give you what you want and do the averages on that.
If A1 is your date, put in this formula: =(MINUTE(A1))+(SECOND(A1)/60)
This will give you the minutes of the time in question and seconds as a fraction of minutes. So if your time is 12:45:15, the return will be 45.25. This will only give you minutes and seconds, regardless of the hour. 1:45:15 pm, 10:45:15 pm & 6:45:15 am will all return 45.25 with that formula.
You can then do your averages of these numbers.
Correction: You can do a formula to give you what you want…
You seem to be correct, Thunderbug - the simple formatting trick works OK for hours:minutes, but not for minutes:seconds! Well, it’s not the first time that Excel’s way of handling time values has gotten me wrapped around the axle.