Calc minutes and seconds difference

[Coach]

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

 

[Peo Sjoblom]

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]

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

 

[Ron Coderre]

Try something like this:

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

Improvement
C1:
=IF(B1>A1,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

 

[Ron Coderre]

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

 

[Coach]

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]

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]

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(F3>D3,0,("00:"&INT(D3/100)&":"&RIGHT(D3,2))-("00:"&INT(F3/100)&":"&RIGHT(F3,2)))

 

[Ron Coderre]

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 F3>D3, 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(D3>F3,0,("00:"&INT(F3/100)&":"&RIGHT(F3,2))-("00:"&INT(D3/100)&":"&RIGHT(D3,2)))

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

XL2002, WinXP

 

[Coach]

Solved. Thanks so much, Ron.

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 F3>D3, 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(D3>F3,0,("00:"&INT(F3/100)&":"&RIGHT(F3,2))-("00:"&INT(D3/100)&":"&RIGHT(D3,2)))

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

XL2002, WinXP

 

[Coach]

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

 

[Coach]

Also, how would you supress zero's on the improvement time (format =mm:ss;@)
when the result might show as 00:41 so it would show :41

 

[Ron Coderre]

Hi, Coach

Here's something to work with:

First, set up the race time input area...
A6: Race1
A7: 12:17 (actually input as 1217)

B6: Race2
B7: 12:16 (actually input as 1216)
(etc across to the right for as many races as you need space for)

Then, set up the variable race comparison section....
A1: Ref1
B1: Ref2

A2: (a race ref number) eg. 3 for the 3rd race
B2: (another race ref number) eg. 2 for the 2nd race
C2: Improvement

A3: =INDEX($A$7:$E$7,1,A2)
B3: =INDEX($A$7:$E$7,1,B2)
C3:
=IF(B3>A3,0,("00:"&INT(A3/100)&":"&RIGHT(A3,2))-("00:"&INT(B3/100)&":"&RIGHT(B3,2)))

You enter the race numbers to be compared in cells A2 and B2.
The formulas in A3 and B3 look up the times for those races.
The formula in C3 calculates the improvement.

Adjust range references to suit your situation.

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

XL2002, WinXP

 

[Coach]

Ron, I have one last problem with the resulting difference field which works
great BUT the mm:ss will keep one "m" displayed to the left of the difference
which I would like to supress along with any leading minute zeros. Here is
an example of a correct result that is dilaying printing with the additional
"m"
A1 = Race Improve
A2 = m 0:21 (21 seconds)

It was created with
=IF(AI3>0,IF(AI3>AH3,0,("00:"&INT(AH3/100)&":"&RIGHT(AH3,2))-("00:"&INT(AI3/100)&":"&RIGHT(AI3,2))),0)

Field is formatted Custom *mm:ss;@
Where does the "m" come from to display, I do not want it!

Chuck

 

[Ron Coderre]

Hi, Chuck

Try using one of these custom number formats:
<format><cells><number tab>
Category: Custom
Type: m:ss;@
or
Type: mm:ss;@


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

XL2002, WinXP

 

[Coach]

Thanks, that did it.

Hi, Chuck

Try using one of these custom number formats:
<format><cells><number tab>
Category: Custom
Type: m:ss;@
or
Type: mm:ss;@


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

XL2002, WinXP