I am subtracting times in excel which works fine for (1:25AM – 3:00AM=-2:05) But once i get to (12:15AM – 11:50PM) I am coming into problems. This should be :25 but it’s showing me 23:35. Any ideas? I am using just the basic A1-B1.
I am using a simple spread sheet representing:
A1=Scheduled Departure Time (3:15AM)
B1=Actual Departure Times (1:22AM)
B1-A1= -1:53
If A1 is 12:15AM
B1 is 11:50PM
C1 ends up 23:35 not 0:25 that it should be.
NO, the formula is correct. The time difference between:
12/05/2011 12:15 AM and 12/05/2011 11:50 PM = 23 hours 35 minutes.
Do you really mean that the times should be:
12/05/2011 11:50 PM and 12/06/2011 12:15 AM
You have gone past midnight into the next day.
EDIT ADDED:
Try this formula:
=IF(A1<B1,(A1+1)-B1,A1-B1)
MIKE
http://www.skeptic.com/
You can’t subtract a “later” time from an “earlier” one.
Excel does not allow “negative times”.
With 1:25 AM in A1 and 3:00 AM in B1, A1-B1 is going to return a cell full of pound signs (#####) since the result would be a negative time.
See here:
http://support.microsoft.com/kb/182247
Besides, the difference between 1:25 AM and 3:00 AM is 1 hour 35 minutes.
What does the -2:05 from your original post represent?
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.