Calculate time in MM:SS.00

[m.wallwoman]

I help coach a swim team, and we need to track the difference in a swimmers time from meet to meet. The time can improve or get worse. Then, we need to calculate the difference between the best time and the regional qualifying time. I've been using this method:


EXAMPLE 1: Seed Time = 35.68 Actual Time = 34.32

EXAMPLE 2: Seed Time = 35.68 Actual Time = 37.97


Format cells as Custom: MM:SS.00.


Example 1 is pretty easy - I use Basic subtraction between the seed time and the actual time at the meet. A1 - A2.


The problem Example 2. Excel doesn't recognize negative numbers with my current setup. I just get ###s. So, I can't use the same calculation in the entire sheet. I have to manually evaluate every calculation.


Once I get all the calculations correct, I need to make the decreases in time green and the increases red (or negative). I have info on this somewhere, but it is the next step in my quest. Now, I"m doing it manually.


Thanks for your help!

 

[NARAYANK991]

Hi ,


Can you clarify whether these values are in minutes or seconds ?


The arithmetic difference between 35.68 and 34.32 = 1.36 ; what is this supposed to represent 1 minute and ??? seconds , or 1 second and 360 milliseconds , or what ?


Formatting the result cell in mm:ss.00 gives 38:24.00 ; what is this ?


Instead of getting the result using a cell format , it would be better if the correct result is first obtained , using the right formulae wherever required.


If positive and negative results are correctly obtained , it is a simple matter ( using conditional formatting ) to get the result cells in green or red as required.


Narayan

 

[Luke M]

Is the value being used in a calculation somewhere else? If not, this might work (although I'm not satisfied as it possibly distorts data).


Change the formula to be:

=ABS(A1-A2)

This will cause the time to display correctly. To indicate positive/negative, we'll add conditional format via these CF rules.

Rule 1:

=A1>A2

Format Green

Rule 2:

A1<A2

Format Red


If the values are being used somewhere else, my recommendation is similar to above, but put my formula in an additional helper row and hide original row. Even though your formula was displaying ####, the actual value was correct. Then, in any later calculations, you refer to the hidden row.

 

[m.wallwoman]

Hi Narayan, thanks for your response. The values are minutes:seconds.milliseconds. So to fix my previous example:


EXAMPLE 1: Seed Time = 35:68.66 Actual Time = 34:32.44

EXAMPLE 2: Seed Time = 35:68.66 Actual Time = 37:97.02


Thanks

 

[m.wallwoman]

Hi Luke, Thanks for your response. I will try the =ABS(A1-A2) formula later today.


The resulting value (time difference) is not being used in a calculation somewhere else. If we do, I'll try the helper row.


Thanks! Will let you know how it goes.

 

[NARAYANK991]

Hi ,


I am still confused. In 35:68.66 how can 68 be seconds ?


Can you put down the result you expect from the calculation 35:68.66 - 34:32.44 ? Similarly , for 35:68.66 - 37.97.02 ?


Narayan

 

[m.wallwoman]

Sorry - I pulled a real example of Minutes:Seconds.Milliseconds. The differences is the expected result. In example 1, the swimmer swam faster at the meet than the seed time. In example 2, the swimmer swam slower at the meet than the seed time.


Example 1: Seed Time = 02:05.47 Meet Time 01:56.39 Difference -00:09.08

Example 2: Seed Time = 01:48.38 Meet Time 01:51.17 Difference +00:02.79


Thanks

 

[NARAYANK991]

Hi ,


Thanks for the clarification.


Luke's formula and the conditional formatting he has suggested should do the job.


Narayan

 

[m.wallwoman]

Thanks for all of your help. The formula Luke suggested works great!


Now, I"m not getting the conditional formatting correct. I used the formula's Luke provided above. Here is what I did:


Selected "Use formula to decide which cells to format"


Rule 1 is =$I$11<$H$11 Green


Rule 2 is =$I$11>$H$11 Red


Regardless of whether the seed time is greater than or less than the meet time, the conditional formatting applies only the 1st rule. And, the first entry in the sheet drives the conditional formatting for all entries on the sheet.


I've tried the rules reverse order and tried using the Stop if True option. Still only the first rule is applied.


Any ideas on this one?


Thank you again for all of your help.

 

[NARAYANK991]

Hi ,


I think you should remove the $ signs.


I have tried the CF formula in cell I11 , as follows :


1. =H11<=I11 , for colour RED


2. =H11>I11 , for colour GREEN


Of course , it can be the other way around , but that should not matter.


When H11 = 01:56.39 and I11 = 02:05.47 , I11 is in RED colour.


When H11 = 01:56.39 and I11 = 01:48.38 , I11 is in GREEN colour.


Narayan

 

[m.wallwoman]

Removing the $ works perfectly. Thank you all for your help.


Now here is the last item to make this a little more automated. I have times from several meets. I need to find the best time from all the meets and evaluate that time against regional qualifying time. If the best time is equal to or less than the regional qualifying time, the swimmer qualifies for the regional meet. If the best time is more than the regional qualifying time, they don't qualify.


So here is the data:


Meet 1 Results 00:37.42


Meet 2 Results 00:34.33


Meet 3 Results 00:35.75


Qualifying Time 00:35.99


Variance -00:01.36


I am now manually finding the best time from all of the Results values and subtracting that time from the Qualifying time value to find the Variance.


As with the earlier example, the variance can be positive or negative. In the above example, the best Results is 1.36 seconds faster than the Qualifying time. Most of the time, the best Result is slower than the Qualifying time.


So, i'm looking for a way to find the lowest value in a range and subtract that value from a constant.


Again, any and all help is appreciated. Thank you.

 

[NARAYANK991]

Hi ,


To find the lowest value in a set of values , use the MIN function , thus :


=MIN(00:37.42,00:34.33,00:35.75)


should give you 00:34.33 ; of course , you cannot use this formula as shown ; instead you need to have the three values in three cells , and give the addresses of these three cells within the MIN formula.


Narayan