# Swim Times - mm:ss.00 format but numerical so you can do formulas and conditional formatting

Copper Contributor

# Swim Times - mm:ss.00 format but numerical so you can do formulas and conditional formatting

Hi there - I have spent a lot of time trying to get the formulas to work for this.  I am doing simple formulas for swim times.

Cell A1 would be a swimmers actual swim time - let's say 50mFree - 00:35.03 (being 35 seconds and 3 milliseconds).

Cell A2 is the time they need to make it to a meet and is 00:34.00 or 34 seconds.

Cell A3 - I want to have a formula in Cell A3 that takes A1 - A2 and if cell A1 is greater than A2 (ie. they still have to drop time by 1:03 seconds to show the result in time format but with a negative and in red (ie. they have to drop 1.03 seconds).  If Cell A1 is less than A2 (ie. say 32 seconds) then the result would show a positive 2.00 seconds and in green (ie. they are faster than the qualifying time by two seconds and would qualify).

I have tried this formula and I get the result I need - but I can't add conditional formatting because it doesn't treat it as a number

=IF(A1 > A2, TEXT(A1 - A2, "mm:ss.00"), "-" & TEXT(A1 - A2, "mm:ss.00"))

Any assistance would be appreciated - there has to be other people doing this for swim times! - but I can't find one that works

JRo

2 Replies

# Re: Swim Times - mm:ss.00 format but numerical so you can do formulas and conditional formatting

A slightly shorter formula:

=IF(A2 < A1, "-", "")&TEXT(ABS(A1 - A2), "mm:ss.00")

Select the cell(s) with the formula and set the fill color to green (this will be the default.

On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Select 'Specific Text ' from the first drop down.
Select 'containing' from the second drop down.
In the box next to it, enter the formula

="-"

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

# Re: Swim Times - mm:ss.00 format but numerical so you can do formulas and conditional formatting

A slight modification of Hans' formatting — to include a plus sign for non-negative numbers — makes it easy to use an additional formula to convert a formatted time difference back to a number, for calculation purposes.

And using Excel 365 or Excel for the web, the LAMBDA function is useful for that calculation.  See cells E2:E6 on Sheet1 in the attached workbook.

I created the defined name TimeDiffValue (a "named formula", loosely speaking) for that LAMBDA function. It's a handy way of doing the conversion; see cells E11:E13 and G13.