Time format

  • Thread starter Thread starter Karel Verhelst
  • Start date Start date
K

Karel Verhelst

I want to do calculations in time tables and would like to see also
negative times in a format such as "- hh:mm" in case the substraction
of 2 times is negative.
It seems this is impossible in excel. Is there a workaround?
 
If you switch to the 1904 date system under Tools, Options, Calculation you
can have negative times. This will cause any date cells to change by 4
years however.
 
Assuming your times are in A1 and A2, try this formula . . .


=IF(A2-A1>=0,A2-A1, "- " & TEXT(A1-A2,"h:mm"))


And to make sure it looks just like all the other time formats when yo
get a negative time, "align right" all your cells using this formula.
Otherwise the negative values will be left justified as they ar
actually entered as text. And be careful if you then use this data i
other calculations, again it is now text.

Hope this helps.

JerryG :
 
JerryG said:
*Assuming your times are in A1 and A2, try this formula . . .


=IF(A2-A1>=0,A2-A1, "- " & TEXT(A1-A2,"h:mm"))


And to make sure it looks just like all the other time formats when
you get a negative time, "align right" all your cells using this
formula. Otherwise the negative values will be left justified as
they are actually entered as text. And be careful if you then use
this data in other calculations, again it is now text.

Hope this helps.

JerryG :) *
Click to expand...

Thanks for the anwer, I found this solution as well, but since it is a
result in text format, one can't use the cell for further calculations
 
Back
Top