Subtracting time with the 24 hr clock/excel

November 28, 2011 at 09:11:59
Specs: Windows XP
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.

See More: Subtracting time with the 24 hr clock/excel

Report •

#1
November 28, 2011 at 09:45:17

Report •

#2
November 28, 2011 at 09:52:04
I have looked there....no luck :(

Report •

#3
November 28, 2011 at 11:20:33
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.


Report •

Related Solutions

#4
December 5, 2011 at 10:14:06
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.

Report •

#5
December 5, 2011 at 12:36:47
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/


Report •

Ask Question