MAX formula not recognizing a time continuation from day before 1

[inspekta76]

I have a MAX formula calculating the difference in time frames and selecting the greater of the two.
i.e. =MAX(I23-E23,I23-D23)
I23 is 02:52 (End of service time)
E23 is 02:12 (ATA)
D23 is 22:15 (ETA-from the night before)
the answer in K23 is 40 but it should be 277
my problem is that the formula must not be recognizing D23 or is giving it a 0 value which would give it a lesser value than I23-E23.
Any help in solving this would be very much appreciated.

 

[AnotherHiggins]

First, read faq68-5827 for information on how Excel deals with times.

Second, you need to know that Excel cannot display negative times. (Well, it can, but you have to switch to 1904 date system which gets confusing.)

Now then, it sounds like these fields all contain only time data, no dates.

That means that Excel is assuming that each of these times are on the same day. How's it supposed to know that 22:15 was last night instead of tonight?

So you are asking for 2:52 - 22:15, which, obviously, returns a negative. 0:40 [!]is[/!] greater than -19:23 (a negative value).

If the value in D23 is always from yesterday, then you can get around this by adding 1 (which is one day) to the other values. That is,
[tab][COLOR=blue white]=MAX((I23+1) - (E23+1), (I23+1) - D23)[/color]
(If the above doesn't make sense, then I again urge you to read faq68-5827.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.