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.
Hello Tom,
Thank you so much for this “Calculating Negative Time” tutorial. It is the closest that I have found to what I’ve been looking for. There is only one element that is missing, and for me this is essential. I would need to be able to calculate the sum of the times from column “C” in your example. However this seems to be impossible, since the cell values are now recognized as text and no longer as time. Do you know of any way to get around this?
I would be overjoyed if you would be able to help me find this answer, as I have been looking for quite a while with no luck.
One way is to put this formula in cell D7 and copy it down to cell D19:
=IF(LEFT(C7,1)="-",RIGHT(C7,LEN(C7)-FIND("-",C7,1))+0,"")Then, to sum the range, meaning sum the positive times and subtract from it the negative times, is the formula
=SUM(C7:C19)-SUM(D7:D19)Thank you so much for the solution! It works!