Now that I have the improvement time worked out (Thanks!) I need to extend
this one step further as I will have 5 different races in which different
completion times are measured against each other for the result.

I can change the formula after each race to reflect the new columns and
paste them OR I can set up a static cell that I can change to reflect the
Previous Race,Current Race columns so that the formula will look to those
columns dynamically based on the input.

Is there a way to do that or am I pushing my luck?

Thanks again Ron

"Ron Coderre" wrote:

Coach:

Per your last post, F3 (5:58) is greater than D3 (5:49)
Which one is the latest time?

Right now, the formula is returning 0 if F3D3, the case above.
So....
If you swap the values in F3 and D3, the formula returns an improvement of
00:09
OR
If you restructure the formula to this, it works:
=IF(D3F3,0,("00:"&INT(F3/100)&":"&RIGHT(F3,2))-("00:"&INT(D3/100)&":"&RIGHT(D3,2)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Coach" wrote:

SO Close! The entry works great. I have 175 runners so that is a huge fix.

The improvement time returns 00:00. Formula audit didn't get me anywhere.

F3=5:58 format(00":"00)
D3=5:49 format(00":"00)
result field=format(mm:ss;@)
=IF(F3D3,0,("00:"&INT(D3/100)&":"&RIGHT(D3,2))-("00:"&INT(F3/100)&":"&RIGHT(F3,2)))

"Ron Coderre" wrote:

Hi, Coach

Try this:

Format the race times as:
Category: Custom
Type: 00":"00

That will make 1216 appear as 12:16, but it won't actually be a time.

Then follow the rest of my previous instructions

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Coach" wrote:

Ron,
I tried this and I think I need to break this into 2 different areas. The
times that I enter do need to print like mm:ss. Right now they are formatted
as h:mm:ss so that it prints right visually but it is still not actually
mm:ss.

I tried the formula you sent but it ended up with 00:00, probably because of
the formatting of the race times. I was not able to custome format to what
you specified as I did not have that option. I had mm:ss and mm:ss.0

I think you know what I am trying to do, can you suggest how I would enter
race times (that prints mm:ss) and still calculate a improvement formula as
well. The format of the race times is the issue I am guessing.

Thanks for the very fast response, this has been an issue for years now.
Coach
"Ron Coderre" wrote:

Minor correcton...I was playing with different race times and posted the
wrong differece between the example times of 1216 amd 1214. The difference,
of course, would be 2 seconds (not 1).

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

For
A1: (RaceTime_1 eg: 1216...meaning 12:16)
B1: (RaceTime_2 eg: 121...meaning 12:14)

Improvement
C1:
=IF(B1A1,0,("00:"&INT(A1/100)&":"&RIGHT(A1,2))-("00:"&INT(B1/100)&":"&RIGHT(B1,2)))
Custom Format C1 as Time (mm:ss;@)

In the above example, C1 displays 00:01 (improvement of 1 second)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Coach" wrote:

Spreadsheet tracks race times for cross country races and needs to be able to
calculate improvement time in mm:ss (or zero if less than previous race).

Also,it would be nice to be able to enter times without using colons or have
to reference the field as h:mm:ss. The race result will always be mm:ss and
the last two digits will always be seconds, the result will never be more
than 60 minutes so hours are a real bother.

A1 (8:52, m:ss) - A2 (8:40, mm:ss) should = 0.12 (12 seconds)

Presently I am using the following: =TEXT(A2-A1,"h:mm:ss"), I only want to
display m:ss, hours are meanlingless