Excel 2007 How to display mm ss format not as a DateTime e g 73 07

Question

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

User · Answer

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

User · Answer

as text    CONCATENATE TEXT cell  d     days   TEXT cell  t     hours   MID TEXT cell  hh mm ss   4 2    minutes   TEXT cell  s     seconds

User · Answer

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

User · Answer

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  lt  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

User · Answer

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

User · Answer

enter the values as 0 mm ss and format as  m  ss  as this is now in the mins  amp  seconds  simple arithmetic will allow you to calculate your statistics

User · Answer

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

[ms-office] Excel 2007: How to display mm:ss format not as a DateTime (e.g. 73:07)?

Examples related to ms-office

Examples related to excel-2007