Time difference

[mac_see]

If I put the time 00:30 in Cell B1 and the time 23:00 in Cell A1 and check out the difference (B1-A1), it gives me an error. Ideally it should be 1:30.

Is there something wrong in the formats?

 

[Tazguy37]

Try using ABS(B1-A1). Hope that helps!

 

[mac_see]

No it gives me 22:30. I am talking about midnight times.

A1 = 2300 1 hour before midnight
A2 = 00:30 half hour after midnight (next day)

XL somehow does not understand that the next time is a next day time.

 

[PA HS Teacher]

Try:
=IF(A2-A1<0,1+(A2-A1),A2-A1)

 

[Zack Barresse]

Not necessarily. Excel will not calculate JUST time. It must have a date with it also. You will not always see the date in lieu of cell formatting, which acts like a mask. Different formats = Different masks.

Change the format of the cells to include dates. They will most likely be 1/1/1900 00:30 AM or something like that. So they are the same day. If you are taking the smaller one from the larger one you are getting an infinite number of # signs ( ###### ) in lieu of a negative time value, which Excel cannot calculate. Or you are getting a result of 22:30.