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).
Pls send me Happy Birthday Exampal excel file
I am preparing all my example workbooks and will be posting them for sale on my Store page in December or January.