How can I subtract and enter times in my charts?

[Warrior]

I am charting times and have various entries such as 46 seconds and 4 fifths
or 1 minute and 12 seconds and 2 fifths (such as the 1/2 mile time and
winning time in a 6 furlong horse race). I need to display and subtract
these times properly. The race results in the newspaper will display the
time as :46.4 and 1:12.2, but excel sees that as 46 and 4 tenths seconds
and 1 minute 12 seconds and 2 tenths. I also need to subtract the smaller
time from the larger time. Any ideas? Thank you kindly.

Steve

 

[David Biddulph]

=INT(A1*24*3600)/(24*3600)+2*MOD(A1,1/(24*3600)) will convert

 

[Warrior Steve]

David, or anyone else,

Thanks for your reply. However, that formula didn't work for times over 1
minute such as 1:12.4. Here is what I'm trying to do:
The race results show the winner of the horse race had fractions 23.87
seconds for the 1/4 mile mark, then 46.65 seconds for the 1/2 mile mark and
finished the 6 furlong race in 1:12.34(1 minute 12 seconds and 34
hundredths). How would I actually enter those numbers on the spreadsheet?And
how would I subtract the 1/4 time from the 1/2 mile time or final winning
time? This can be applied to any race, not just horse races. Again, thanks.

Steve

 

[David Biddulph]

For me the formula happily converts 1:12.2 to 1:12.4. You may wish to check
that you've got copied and pasted the formula as I gave it.

It is, of course much better not to be using that strange notation for 5ths
that you had in your original message, and not to have to use the conversion
formula, so the numbers as you've given in your most recent message (with
hundredths of a second) can be entered simply as 0:23.87, 0:46.65, and
1:12.34. You can format the cells as something like [m]:ss.00.

You can do a straight subtraction of cells in that format, giving the
results 0:22.78 and 0:48.47.

 

[Warrior Steve]

David,

That formula works fine now. My 1st message contained 5ths, but I found a
web site that shows the actual hundredths(more accurate) and that is why I
changed my 2nd reply. I will use the format you provided. Thank you for
your replies.
Steve

For me the formula happily converts 1:12.2 to 1:12.4. You may wish to
check that you've got copied and pasted the formula as I gave it.

It is, of course much better not to be using that strange notation for
5ths that you had in your original message, and not to have to use the
conversion formula, so the numbers as you've given in your most recent
message (with hundredths of a second) can be entered simply as 0:23.87,
0:46.65, and 1:12.34. You can format the cells as something like
[m]:ss.00.

You can do a straight subtraction of cells in that format, giving the
results 0:22.78 and 0:48.47.
--
David Biddulph

David, or anyone else,

Thanks for your reply. However, that formula didn't work for times over 1
minute such as 1:12.4. Here is what I'm trying to do:
The race results show the winner of the horse race had fractions 23.87
seconds for the 1/4 mile mark, then 46.65 seconds for the 1/2 mile mark
and finished the 6 furlong race in 1:12.34(1 minute 12 seconds and 34
hundredths). How would I actually enter those numbers on the
spreadsheet?And how would I subtract the 1/4 time from the 1/2 mile time
or final winning time? This can be applied to any race, not just horse
races. Again, thanks.

Steve