Time Difference Formula

[George]

Excel 2007
I am looking for help with a formula that calculates the difference between
two times. The one I am using now will not return a negative value.
ie. Cell A1 = 1:45 PM, Cell B1 = 1:43 PM, I would like Cell C1 to show -2
min., and at the same time show a positive number if Cell B1 is greater.

 

[Mike H]

George,

Tools|Options - Calculation and select the 1904 date system and you can
display negative time

+b1-A1 and format as [m]

The down side of doing this is that all other existing dates in your
workbook will be 4yrs and 1 day out.

Mike

 

[JLatham]

For some reason I can't see what Mike H wrote, so this may be an echo of his
input.

See this site for some ways to deal with negative times
http://www.brainbell.com/tutorials/ms-office/excel/Display_Negative_Time_Values.htm

Here's a formula that will handle it, as displayed but remember that the
result is actually text and not a numeric time value you can work well with
later on:
=IF(A1>=B1,TEXT(A1-B1,"hh:mm"),TEXT(B1-A1, "-hh:mm"))

 

[JLatham]

System's giving me headaches this morning - can't read Mike H's reply, so
this may be a repeat of his.

See this site for some ways to deal with negative times in Excel
http://www.brainbell.com/tutorials/ms-office/excel/Display_Negative_Time_Values.htm

And here's a formula that should help, just keep in mind that the result is
text and not numeric values that you can easily do math with later on:
=IF(A1>=B1,TEXT(A1-B1,"hh:mm"),TEXT(B1-A1, "-hh:mm"))