Tom’s Tutorials For Excel: Calculating Negative Time

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
=IF($A$4>B7,"-"&TEXT(ABS(B7-$A$4),"[hh]:mm:ss"),B7-$A$4)

The custom format in cell A4 and the cells in range B7:C19 is
[hh]:mm:ss

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).