How can I subtract and enter times in my charts?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Warrior - 08 Dec 2006 01:58 GMT

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

Reply to this Message

David Biddulph - 08 Dec 2006 12:04 GMT

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

Signature

David Biddulph

>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
[quoted text clipped - 6 lines]
>
> Steve

Reply to this Message

Warrior Steve - 09 Dec 2006 01:08 GMT

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

> =INT(A1*24*3600)/(24*3600)+2*MOD(A1,1/(24*3600)) will convert
>>I am charting times and have various entries such as 46 seconds and 4
[quoted text clipped - 7 lines]
>>
>> Steve

Reply to this Message

David Biddulph - 09 Dec 2006 12:49 GMT

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.

Signature

David Biddulph

> David, or anyone else,
>
[quoted text clipped - 21 lines]
>>>
>>> Steve

Reply to this Message

Warrior Steve - 11 Dec 2006 03:10 GMT

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.
[quoted text clipped - 33 lines]
>>>>
>>>> Steve

Reply to this Message