Tom’s Tutorials For Excel: Calculating Negative Time
Here’s an example for calculating the difference between times, and expressing a negative result when a larger time is subtracted from a smaller time.
In the picture, runners are listed for comparing their 1000 meter run times against the record time. Two runners eclipsed the record, shown in column C with a negative sign in front of their calculated Over/Under time difference.
The formula in cell C7 and copied down to cell C19 is
The custom format in cell A4 and the cells in range B7:C19 is
To call attention to the record-breakers, the data range was selected from cell A7:C19 and conditionally formatted with the custom rule
=$A$4>$B7 (note the relative and absolute references).