time formulas

[vdmbqb]

I have times generated in h:mm in cell A1 and A2 I need a formula in A3 that
subtracts A2 from A1 and produces the difference (either negative or
positive) in h:mm.
For instance, the time in A1 is 6:15 and the time in A2 is 6:00 I need A3
to return -:15 or -0:15. All I can get is ###########.

Thank you in advance for any help offered.

 

[Pete_UK]

If you want to make use of negative times you will have to switch to
the 1904 date system rather than the default 1900. However, this can
cause problems if you need to put the file on a system with 1900 as
the base - dates will appear to be 4 years and one day out.

Hope this helps.

Pete

 

[FSt1]

hi
at pete stated the 1900 date keeping system will not show negative times.
the 1904 system will but it was developed for mac and will be out of cinque
with windows based pc systems.
you could have a formula that converts the negative time to a positive time.
=IF(A2-B2<0,(A2-B2)*-1,A2-B2)
then in a column next to it, something like this.....
=IF(A2-B2<0,"Neg","Pos")
but this would take 2 cells. and present a math problem should you need to
total the times.
another way was posted by dave peterson.
=IF(A2<B2,"-","")&TEXT(ABS(B2-A2),"mm:ss")
the negative time would be in 1 cell but you wouldn't be able to do math
with the above formula.

no easy way to handle negative times in excel.

regards
FSt1