How do i enter negative times in Excel & how can they be 'fiddled.

[Guest]

How do i enter negative times in Excel & how can they be 'fiddled with' to
allow SUM functions etc... to work correctly? When I try to enter a negative
time in time format [h]:mm an error message pops up saying put a ' in front
.. This does allow the negative time to be entered but when this cell is
incorporated in a SUM funtion it is ignored. There is probably a simple
solutionto this, but as an excel novice it has me stuck!

 

[Bernie Deitrick]

Stuck!,

Use John Walkenbach's extended date difference add-in, available at

http://www.j-walk.com/ss/excel/files/xdate.htm

HTH,
Bernie
MS Excel MVP

 

[Jason Morin]

You can still use the negative time values (########) in
your time calculations. What you can do is hide the
actual cell with the formula that subtracts the times
(=B1-A1) and reference this cell in any calculations.

But for display purposes, use this formula in another
cell:

=IF(B1>=A1,TEXT(B1-A1,"[h]:mm:­ss"),TEXT(A1-B1,"-
[h]:mm:ss"))

where B1 = later time, A1 = earlier time.

HTH
Jason
Atlanta, GA