Locate highest status and return "XYZ" based on what is located into new cell same row

[nymanns]

I have looked (Googled) many places but not really found what I was looking for.
Don't know if I search/ask in the wrong way, but now I'll try in here.

In regards to a shipment flow, we have different stages in which the package will go through before being delivered.

There is a PICKUP phase, DEPARTURE phase, ARRIVAL phase & a DELIVERY phase
In each phase I have dates (yyy-mm-dd)
I can have ESTIMATED dates & I can have ACTUAL dates (Sample sheet attached)
Sample setup is similar but not identical to the "live" file

I have 8 columns where cells can be

• Completely blank
• Have formulas, which either returns a date or returns nothing
• Simply has a date (No formulas)

For the people knowing the transport business, it is well known that we can have ANY sort of combination of blanks / dates in ALL 8 columns.
Meaning that in one phase we might have only one date, in the next phase we might have both dates and in another phase we don't have any dates at all..

I have tryed to illustrate in the sheet what I would like to have as a result.
Basically I want to see the highest phase where there is a date

The answer might be obvious & right in front of me, but right now I have come up empty...
Hope someone can help..

Regards
Henrik

[AliGW]

Will the dates always be chronological from left to right?

[Pete_UK]

You can use the array* formula in J5:

=MAX(IF(B5:I5<>"",$B$2:$I$2))

then copy down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >.

If you want to see the colours that you show in your sample file then you will have to apply conditional formatting with 8 different rules.

Hope this helps.

Pete

[nymanns]

@ AliGW

Mostly yes, but unfortunately there can be exceptions..

It can happen that the estimated date for a higher phase is sooner than the actual date from the phase prior.
i.e. 01-04-2016, 03-04-2016, 02-04-2016, 04-04-2016, etc... (est. pickup, act. pickup, est. departure, act. departure, etc..)

Sample file updated..

[AliGW]

But will the last date recorded on any row be the highest? If so, Pete's formula will work.

[nymanns]

@ Pete_UK

Spot on...
Works in this sample..

But I will need to test when there are other columns in between these 8..

Hang on..

[AliGW]

Do I detect a shifting of goalposts???