Excel 2007: How to display mm:ss format not as a D

问题:

I need to create and Excel table that computes daily training times. each row has the following fields: Date, Distance, Time and Minutes/Km.
My main problem is that I want to display the Time in format of mm:ss. For example: 24 min and 3 sec should be 24:03, but "Excel" turns it to 00:03:00 (3 min after midnight). I've tried setting up "special formatting" but still "Excel insists" on formatting the hours. More over, training may exceed 60 minutes and I still want it only as mm:ss
My second question is after I'm done with the formatting issue, what is the simple way to compute Time/Distance?

回答1:

enter the values as 0:mm:ss and format as [m]:ss

as this is now in the mins & seconds, simple arithmetic will allow you to calculate your statistics

回答2:

To make life easier when entering multiple dates/times it is possible to use a custom format to remove the need to enter the colon, and the leading "hour" 0. This however requires a second field for the numerical date to be stored, as the displayed date from the custom format is in base 10.

Displaying a number as a time (no need to enter colons, but no time conversion)

For displaying the times on the sheet, and for entering them without having to type the colon set the cell format to custom and use:

0/:00

Then enter your time. For example, if you wanted to enter 62:30, then you would simply type 6230 and your custom format would visually insert a colon 2 decimal points from the right.

If you only need to display the times, stop here.

Converting number to time

If you need to be able to calculate with the times, you will need to convert them from base 10 into the time format.

This can be done with the following formula (change A2 to the relevant cell reference):

=TIME(0,TRUNC(A2/100),MOD(A2,100))

• =TIME starts the number to time conversion
• We don't need hours, so enter 0, at the beginning of the formula, as the format is always hh,mm,ss (to display hours and minutes instead of minutes and seconds, place the 0 at the end of the formula).
• For the minutes, TRUNC(A2/100), discards the rightmost 2 digits.
• For the seconds, MOD(A2,100) keeps the rightmost 2 digits and discards everything to the left.

The above formula was found and adapted from this article: PC Mag.com - Easy Date and Time Entry in Excel

Alternatively, you could skip the 0/:00 custom formatting, and just enter your time in a cell to be referenced of the edge of the visible workspace or on another sheet as you would for the custom formatting (ie: 6230 for 62:30)

Then change the display format of the cells with the formula to [m]:ss as @Sean Chessire suggested.

Here is a screen shot to show what I mean.

回答3:

Excel shows 24:03 as 3 minutes when you format it as time, because 24:03 is the same as 12:03 AM (in military time).

Use General Format to Add Times

Instead of trying to format as Time, use the General Format and the following formula:

=number of minutes + (number of seconds / 60)

Ex: for 24 minutes and 3 seconds:

=24+3/60

This will give you a value of 24.05.

Do this for each time period. Let's say you enter this formula in cells A1 and A2. Then, to get the total sum of elapsed time, use this formula in cell A3:

=INT(A1+A2)+MOD(A1+A2,1)

Convert back to minutes and seconds

If you put =24+3/60 into each cell, you will have a value of 48.1 in cell A3.

Now you need to convert this back to minutes and seconds. Use the following formula in cell A4:

=MOD(A3,1)*60

This takes the decimal portion and multiples it by 60. Remember, we divided by 60 in the beginning, so to convert it back to seconds we need to multiply.

You could have also done this separately, i.e. in cell A3 use this formula:

=INT(A1+A2)

and this formula in cell A4:

=MOD(A1+A2,1)*60

Here's a screenshot showing the final formulas:

回答4:

If you are using hand inputted data, you can enter your data as mm:ss,0 or mm:ss.0 depending on your language/region selection instead of 00:mm:ss.

You need to specify your cell format as [m]:ss if you like to see all minutes seconds format instead of hours minutes seconds format.

回答5:

as text:

=CONCATENATE(TEXT(cell;"d");" days ";TEXT(cell;"t");" hours ";MID(TEXT(cell;"hh:mm:ss");4;2);" minutes ";TEXT(cell;"s");" seconds")

回答6:

5.In the Format Cells box, click Custom in the Category list. 6.In the Type box, at the top of the list of formats, type [h]:mm;@ and then click OK. (That’s a colon after [h], and a semicolon after mm.) YOu can then add hours. The format will be in the Type list the next time you need it.

From MS, works well.

http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx