- November 14, 2013 at 4:43 am #30202
Hello,I need to subtract race times. I have a runner that runs a 5K race in 19:06:97. Another runner runs it in 25:01:97. A third runner does it in 29:09:07. I need to subtract the second time from the first time. Then the third time from the second time etc. This had nothing to do with the time of day but Excel can not seem to get around this.I set up a form to do this but I am having trouble keeping the time format so the cells show the proper times. I keep getting 7:06:90 PMAlso, when I try to subtract 25:22:96 from 24:40:12 ie 25 minutes, 22 seconds and 96 hundredths of a second from 24 minutes etc. Excel can not do this and give me a #VALUE! error. Thank s for your time with this. I really would appreciate the help.November 14, 2013 at 4:48 am #34306
I was able to do the calculation by the following method:1) I selected the cells that hold the time (in my sample B2 â€“ faster runner and B3 â€“ slower runner).2) I opened the format cells dialog box. In 2007, Home tab/Number group/click drop-down arrow next to the default format/click More Number Formats in the list that appears. In 2003, click the Format menu, click Cells, click the number tab.3) In the list of categories on the left, I chose Custom. Then in the list at the right I found one to match the type of values in this scenario: mm:ss.0 (Be sure that the punctuation between seconds and tenths of seconds is a period â€“ not a colon.) If you donâ€™t see that choice inNovember 14, 2013 at 4:56 am #34307
Note this example:Cell A1=26:56:97Cell A2=25:56:97The answer should be a difference of 1 minute. I need the answer to look like this: 1:00:00. The third segment of your time should be expressed as a decimal like this:Cell A1 = 26:56.97 (which would indicate 26 minutes, 56 seconds and 97 one-hundredths of a second.Cell A2 = 25:56.97When I entered these values with the decimal points between the last two numbers the formula works.I got this info here at an article about calculating elapsed time. http://ittrainingtips.iu.edu/excel/calculating-elapsed-time-in-excel/04/2009
You must be logged in to reply to this topic.