Excel 2007 : Calculating difference in times, expressing as a negative number

**evebutterly** · 07-06-2009, 12:09 PM

I want to calculate the difference between two columns of time: estimated and actual (minutes and seconds). Occasionally, the difference will be a negative number. I keep getting ##### as a result. An example of what I would like to achieve:

Est. Act.
Time Time +/-
1:00 1:05 :05
:35 :45 :10
:45 :30 -:15
:50 :40 -:10
__________________________
3:10 3:00 -:10

**squiggler47** · 07-06-2009, 12:29 PM

=IF(StartTime<EndTime,"-","") & TEXT(ABS(StartTime-EndTime),"hh:mm:ss")

with the cell formatted as text will give 00:00:05 or -00:00:05

**DonkeyOte** · 07-06-2009, 12:30 PM

Unfortunately you can't display both positive & negative time in a time format if running on 1900 date system (switching to 1904 purely for this purpose is not advised).

So you have a few options - either

a) store in non-time value terms by * result by 24

C2: =24*(B2-A2)

b) store in non-time value terms by * result by 24 & subsequently use DOLLARFR to display in time-esque format (ie 0.05)

C2: =DOLLARFR(24*(A2-B2),60)
Formatted to Number & 2 decimals

c) store negative times as text, eg:

C2: =REPT("-",B2<A2)&TEXT(ABS(B2-A2),"hh:mm")

Option a is the most obvious as it allows for summation... though you could use A6: SUM(A2:A5) & B6: =SUM(B2:B5) and use any of the above to generate the result