Conditional Formatting - Unique Request?

[rushsoccer]

Hi everyone,

I've searched around for quite a bit trying to answer my question to no avail, so I apologize if this has been answered elsewhere....

I am trying to make up a Flight Comparison worksheet as I do a lot of group travel comparisons, and I would like to make it easier on myself with formulas and formatting.

What I want to do is record all flight departure times and arrival times (easy enough) and calculate the total flight time, which I did with this formula:

=CONCATENATE(TEXT(B4-B3,"H")," Hours ", MINUTE(B4-B3), " Minutes")

...An example return is "4 Hours 20 Minutes"

Where I get stuck is trying to apply conditional formatting to the longest flight time and the shortest flight time (I would like a simple red fill for longest, green for shortest.) I would like to do this with layover times as well.

I like the concatenated output because it's easier for our team managers/coaches to read. However, I'm not sure how to make Excel
a) read and interpret this row as data and highlight the min/max values accordingly,
OR
b) highlight the nice concatenated row according to a hidden row of data I can create (a number-formatted subtraction of Arrival Time minus Departure Time.)

I am open to suggestions, and thank you!

 

[shg]

How about instead using =B4-B3 and format as h "hours" m "minutes" and then use CF in the conventional way.

 

[rushsoccer]

How about instead using =B4-B3 and format as h "hours" m "minutes" and then use CF in the conventional way.

Hello,
yes I thought about doing that, but I like how the output "4 Hours 20 Minutes" looks as opposed to "4:20"..

 

[shg]

I think you didn't try what I suggested ... the output will look the same as it does now.