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

So what's your question?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Coach" wrote in message
...
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

How can I do the following:
1: avoid keying ":" for the time which is mm:ss
2: Return zero on a negative result instead of #VALUE!
3: Have time difference show in mm:ss format instead of h:mm:ss format

Sorry, I was not specific enough...hope this helps

"Peo Sjoblom" wrote:

So what's your question?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Coach" wrote in message
...
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

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

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

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

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

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