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?
This question is related to
ms-office
excel-2007
One convenient trick to entering elapsed times into Excel is to have two zeros and a colon before the number of minutes, details follow. For copy and paste operations into Excel without have to worry about formatting at all one can use the format 00:XX:XX where XX are two digits totaling < 60. In that case, Excel will echo 0:XX:XX in the cell contents displayed and store the data as 12:XX:XX AM. If one pastes data in a 00:XXX:XX format into Excel, or 00:XX:XX where either XX > 59 this will be converted into a fraction of a day.
For example, 00:121:12 becomes 0.0841666666666667, which if multiplied by the number of seconds in a day, 86,400, becomes 7272 s. Next, 00:21:12 would by default show 0:21:12 stored as 12:21:12 AM. Finally, 00:21:60 becomes 0.0152777777777778, also a fraction of a day.
This suggestion is made merely to avoid having to worry about specific formatting in Excel, and letting the program worry about it. Note, for Excel data internally formatted as 12:XX:XX AM one can only use certain Excel commands, for example, one can take an average. However, subtraction will only work when the result is a positive number. Such that converting times into seconds, fractions of a day, or other real number is suggested for access to more complete arithmetic operation coverage.
For example, if one has a column of mixed time formats, or times that are negative and will not display, if one changes the number formatting to General, all the times will be converted to fractions of a day.
as text:
=CONCATENATE(TEXT(cell;"d");" days ";TEXT(cell;"t");" hours ";MID(TEXT(cell;"hh:mm:ss");4;2);" minutes ";TEXT(cell;"s");" seconds")
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 conversion0,
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).TRUNC(A2/100),
discards the rightmost 2 digits.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.
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
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.
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).
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)
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:
Source: Stackoverflow.com