Forum Discussion
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
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.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.
