Subtracting time in

QuestionEdit

I have two times that I need to subtract, for instance I need to subtract 11/30/10 8:00 from 10/31/10 6:15. How would I do that? I know thatwill not show a negative amount of time, but I need it to do so.

AnswerEdit

A1: 11/30/10 8:00 A2: 10/31/10 6:15 if you want the answer in hours: (times are being treated as in the morning) =ABS(A2-A1)*-24 gives a -721.75

This is a number you can do further math on.

if you just want to display a string that looks like a negative time value ="-"&TEXT(ABS(A2-A1),"dd ""Days"" hh:mm") that display -30 Days 01:45

If you really want to show a negative time without doing anything special, you can go into Options and select the 1904 time system which will display negative time. However, any existing dates will loose approximately 4 years. Generally I would say doing that would be a bad choice, but I don't know what you are doing or what your environment is. If you exchange a file with someone using the 1900 date system (the default in Windows based excel, the MAC uses the 1904 as a default), then they would add ~4 years to any date you passed them.