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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Calculating Negative Time
  1. Dinesh says:

    Pls send me Happy Birthday Exampal excel file

Leave a Reply

Your email address will not be published. Required fields are marked *

*