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

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!

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


"stuck!" wrote in message
...
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!

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

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

=3DIF(B1=3DA1,TEXT(B1-A1,"[h]:mm:=ADss"),TEXT(A1-B1,"-=20
[h]:mm:ss"))=20

where B1 =3D later time, A1 =3D earlier time.

HTH=20
Jason=20
Atlanta, GA=20


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