Calculation Issues

How to Calculate Race Time Differences and Add Conditional Formatting in Excel

Algirdas Jasaitis Oct 8, 2026 869 views

Question details

The user needs to calculate the difference between swimming race times formatted in minutes, seconds, and hundredths, and visually represent positive or negative splits using conditional formatting.

How to Calculate Race Time Differences and Add Conditional Formatting in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking race times where split differences must display accurately without negative time errors, and color-coded rules are required to easily spot faster or slower times.
Observed behavior: The user wants to format time correctly to hundredths of a second, calculate the time gap between two cells, and apply green or red conditional formats based on the performance.

Before you start

Verify your data entry method. Excel often interprets standard inputs like '12:34' as 12 hours and 34 minutes, so ensure you enter times as '0:12:34' or '12:34.0' to correctly log minutes, seconds, and hundredths.

Solution 1Recommended

Use ABS Formula and Custom Time Formatting

Combine the ABS function to bypass Excel's negative time limitations with a custom format to display minutes correctly, then apply conditional formatting rules.

By default, Excel struggles to display negative time differences, often returning a row of hash marks (#####). To circumvent this, you can calculate the absolute (unsigned) time difference using the ABS function. You can then use Conditional Formatting to apply red and green colors to indicate whether a time was faster or slower.

Apply Custom Time Format

Select the cells containing your race times. Right-click and choose 'Format Cells'. Go to the 'Custom' category and type '[mm]:ss.00' in the Type box. The brackets around 'mm' ensure that times over 59 minutes are not automatically rolled over into hours.

Calculate the Unsigned Difference

In your result cell (e.g., D2), type the formula =ABS(B2-C2). This subtracts the time in C2 from B2 and returns a positive number, bypassing Excel's negative time display error.

Add Green Conditional Formatting

Select your result cells. On the 'Home' tab, click 'Conditional Formatting', then 'New Rule'. Choose 'Use a formula to determine which cells to format', enter =B2>=C2, click 'Format', and choose a Green fill color.

Add Red Conditional Formatting

With the cells still selected, create another 'New Rule' using the formula =B2<C2. Click 'Format', choose a Red fill color, and click 'OK' to apply.

Use ABS Formula and Custom Time Formatting

Formatting Times Over 10 Minutes: Using [mm] instead of mm in your custom format is especially important for swimming times or race splits that exceed 9 minutes and 59 seconds, guaranteeing accurate data display.

Track Race Times in WPS Office

Calculate Race Times Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful time calculation functions, custom formatting options, and easy-to-use conditional formatting, allowing you to track and visualize race times perfectly for free.

1. Open WPS Spreadsheet: Launch WPS Office and open your race time tracking workbook or create a new blank spreadsheet.
2. Format Cells for Race Times: Highlight your data cells, right-click to select 'Format Cells', navigate to the Custom tab, and input the format [mm]:ss.00.
3. Calculate and Color Code: Use the =ABS() function to calculate the time gap. Next, use the Conditional Formatting tool located on the Home tab to quickly assign your red and green highlight rules.

Free, lightweight, and fast alternative to Microsoft Excel.Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Supports advanced custom time formatting like [mm]:ss.00 for precision tracking.Intuitive conditional formatting interface for immediate visual data insights.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel display ##### when calculating time differences?

Excel's default date and time system cannot display negative times. If you subtract a larger time from a smaller one, it results in a negative value and displays #####. Using the ABS function (e.g., =ABS(B2-C2)) calculates the absolute difference, resolving this error.

How do I format time to show hundredths of a second in Excel?

To show hundredths of a second, you must apply a custom number format. Select the cell, press Ctrl+1 to open the Format Cells dialog, select 'Custom', and enter the format code mm:ss.00 or [mm]:ss.00.

Why is my 12-minute race time appearing as 12 hours?

When you type '12:34' into a cell, Excel automatically assumes the first number is hours and the second is minutes. To correctly input 12 minutes and 34 seconds, type it as '0:12:34' or '12:34.0'.

Excel & Data Problems