Time calculations

[Eric Parker]

I have an Excel spreadsheet with mile split times for cross country runners
in one column (e.g. 5:08, meaning 5 minutes, 8 seconds) and 2 mile split
times in the next column (e.g. 10:15). The third column is the difference
between the mile and 2 mile times which is the time the runner ran for the
second mile. How can I have Excel do this calculation, and what format do I
need to put it in considering I do not need hours, days, dates, years, AM,
PM, etc.? I also would like to have a fourth column which displays the
difference (in seconds) between the third column and the first column, which
can be either a positive or a negative number to show how much the runner
sped up or slowed down.

 

[Pete_UK]

Although you may have entered 5:08 (meaning 5 minutes and 8 seconds),
Excel will probably have taken this to mean 5 hours and 8 minutes. You
will need to enter it as 0:5:08 or as 5:08.0 for Excel to interpret it
correctly. Format the cell as h:mm:ss to see what Excel has done.

To convert to your proper time, you will need to divide each time by
60 - an easy way is to put 60 in a blank cell somewhere and click
<copy> on that cell. Then highlight all the cells with those times in
and Edit | Paste Special | Values (check) | Divide (check) | OK and
<Esc>. Then you can delete the original cell.

You will need to format the cells as [m]:ss.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I forgot to answer the question. If you have times in columns A
and B, assuming a header, then in C2 you can have this for the second-
mile time:

=B2-A2

Format the cell as [m]:ss

In D2 you can use this formula:

=(C2-A2)*60*60*24

and format this cell as General or Number with 0 dp.

Copy the formula down as necessary.

Hope this helps.

Pete

Although you may have entered 5:08 (meaning 5 minutes and 8 seconds),
Excel will probably have taken this to mean 5 hours and 8 minutes. You
will need to enter it as 0:5:08 or as 5:08.0 for Excel to interpret it
correctly. Format the cell as h:mm:ss to see what Excel has done.

To convert to your proper time, you will need to divide each time by
60 - an easy way is to put 60 in a blank cell somewhere and click
<copy> on that cell. Then highlight all the cells with those times in
and Edit | Paste Special | Values (check) | Divide (check) | OK and
<Esc>. Then you can delete the original cell.

You will need to format the cells as [m]:ss.

Hope this helps.

Pete

I have an Excel spreadsheet with mile split times for cross country runners
in one column (e.g. 5:08, meaning 5 minutes, 8 seconds) and 2 mile split
times in the next column (e.g. 10:15).  The third column is the difference
between the mile and 2 mile times which is the time the runner ran for the
second mile.  How can I have Excel do this calculation, and what format do I
need to put it in considering I do not need hours, days, dates, years, AM,
PM, etc.?  I also would like to have a fourth column which displays the
difference (in seconds) between the third column and the first column, which
can be either a positive or a negative number to show how much the runner
sped up or slowed down.- Hide quoted text -

- Show quoted text -

 

[FSt1]

hi
just too add a tad to what pete said.
excel keeps track of datese as a number based on the 1900/1904 date keeping
system and time as a decimal value of 1 day. so 5 minutes 8 seconds would be
0.003553241 of 1 day and would display in the formula bar as 12:05:08 AM or
5 minutes 8 seconds past midnight.
see this site for more details......
http://support.microsoft.com/kb/214330

to do the math. since excel views time as numbers you can just subtract one
time from another. with 5:08 in A2 and 10:15 in B2....
in c2 enter.....=B2-A2
which would yield 5:07
as to your last question, the 1900 date system will not display negative
numbers.
instead you get ####### any time the results are negative. you can use a
formula to prevent this..
=mod(C2-B2,1)
this would prevent the number from displaying negative but with the date
formated as mm:ss and desplayed as 12:05:08 AM in the formula bar, the
results would be unexpected since the results may put the time back past
midnight.
59:59 and displayed in the formula bar as 11:59:59 PM
yeah i know. wierd.
the 1904 date system will display negative numbers but switching would add 4
years and 1 day to all the other dates you may have in your other files. and
you might be out of sinc with other pc's. see the above site for more details.

regards
FSt1