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.

Lot's of great Time & Date functions can be found here: http://www.ozgrid.com/Excel/date-ti...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have looked there....no luck :(

Something in your intial post doesn't make sense . 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.

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.

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

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History