Formula for selecting greater of two values 1

[inspekta76]

HERE GOES...column at top
ETA(A1) ATA(B1) END(C1) TIME(D1)
10:15 10:30 10:46 16 =HOUR(C1-B1)*60+MINUTE(C1-B1)
So normally I need the C1-B1 for stats...but IF C1-A1 is greater then I need that number-PLEASE HELP.
The formula I have in D1 is to convert the time difference into total minutes A1,B1,C1 are time fields and D1 is a general field.
Basically I would like to have a formula which chooses the greater of the two time differences C1-A1 or C1-B1
Thanks for any help

 

[AnotherHiggins]

Check out the MAX formula

example:

=MAX(C1-B1, C1-A1)

[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.

 

[inspekta76]

Thanks for the info-I've combined what you wrote with what I had it looks like =MAX((HOUR(I5-E5)*60+MINUTE(I5-E5)),(HOUR(I5-D5)*60+MINUTE(I5-D5)))
This formula works great except in cases where End time happens to be earlier than than the ETA. Example (I5 is 10:00 and D5 is 09:50) Excel gives a #NUM! error I assume because it creates a neg integer. Anyway around it? Help would be appreciated.
Thanks

 

[inspekta76]

Thanks for the info-I've combined what you wrote with what I had it looks like =MAX((HOUR(I5-E5)*60+MINUTE(I5-E5)),(HOUR(I5-D5)*60+MINUTE(I5-D5)))
This formula works great except in cases where End time happens to be earlier than than the ETA. Example (I5 is 09:50 and D5 is 10:00) Excel gives a #NUM! error I assume because it creates a neg integer. Anyway around it? Help would be appreciated.
Thanks

 

[AnotherHiggins]

I didn't really look at your formula last night. Sorry about that, it was late.

There are better approaches to dealing with the time values.
Replace your last formula with
[tab][COLOR=blue white]=MAX(I5-E5,I5-D5)[/color]
The trick is to format the cell with the formula as Custom > [mm]

The square brackets around the ms tells excel to report the total number of minutes.

If you really need the number to be formatted as general, then you can convert minutes to a general time by multiplying by 24 (hours in a day) and by 60 (minutes in an hour). So you could use this:
[tab]=MAX(I5-E5,I5-D5)*24*60
or, to shorten it up a bit more,
[tab][COLOR=blue white]=MAX(I5-E5,I5-D5)*1440[/color]
Here be sure to format the cell with the formula as General

Now, as for your last question - Excel doesn't 'do' negative time*.

But the good news is that either of the above alternate solutions should work for you (formatting as [mm] or multiplying by 1440 and formatting as general).

FYI: When you're dealing with straight time (not converting to minutes-only), you can get around Excel's negative-time problem by checking to see if start time is greater than end time and, if it is, add a day to the end time. (NOTE: This assumes you won't deal with any times greater than 24 hours).

In Excel, one day is stored as 1, so your formula would look like this:
[tab][blue]=IF(MAX(I5-E5,I5-D5)<0, MAX(1+I5-E5,1+I5-D5), MAX(I5-E5,I5-D5))[/blue]

Have a look at faq68-5827 for information on how Excel deals with dates and times.

*Actually it can, but you have to use 1904 Date System

[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.

 

[inspekta76]

Brilliant-huge help, massive time and headache saver!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks a bunch!!!!!!!!!!!!!!!!!