I am having problems with excel taking one column of time away from another, lets call them A and B (each row is taken from the same occasion). I want to find the difference between A and B (either of which might be the later time) and I want that given in time as either a positive or negative figure. In column C I'm using a basic formal '=SUM(B2-A2)' At first it wouldn't give negative time values (just #######) but I then found the 1904 date setting. However now I find some values which cross midnight are wrong. For example: A = 23:34, B = 00:05, C should = 00:31 but instead gives -23:29 I have many thousands of entries and can't start putting dates in with the times. Can anyone please suggest a way around my problem? Really need help on this one!

You have to correct by adding 24 hrs or subtracting 24 depending on which crosses midnight. This would assume it would be less than 24 hour difference.

Yes the difference would always be less than 24 hours. What formula could I use to do this?

First un-tick the 1904 option, and try this: =IF(A1>B1,B1+1-A1,B1-A1) MIKE http://www.skeptic.com/

That works for the example I gave, but not for others. For A = 01:00, B = 00:40 if gives 23:40 instead of -00:20 For A = 00:50, B = 23:50 if gives 23:00 instead of -01:00

I’ve almost solved my problem. There are three main examples of case for values of A and B below and their correct solutions. A B C (should be) D (value of =SUM(B-A) 23:34 00:05 00:31 -23:29 01:00 00:40 23:40 -00:20 00:50 23:50 23:00 23:00 Using a new column E and cell F1 with the value 10:00 (Note difference between times A and B will always be under 10 hours) I have been able to gain the correct answer with two formula’s For the first row =IF(C2<-F1,24-SUM(A2-B2)) For the second two rows =IF(D3>F1,-(24+SUM(A3-B3)), D3) Can anyone think of a way I can combine these two formulas?

In case you haven’t figured it out yet, Excel does not like negative numbers while dealing with Time math. In Excel, when you enter a time without a date, Excel assumes it is January 1, 1900, and it puts a 0 in for the date component of the serial number which is how it deals with dates. So ALL your times are considered to be on the same day…. It is much simpler to just add a date before the times and then do the math. MIKE http://www.skeptic.com/