Choose MAX date from several columns, based on TODAY's DATE

JennyHoA20181 

XMATCH (and XLOOKUP) are quite new. They've been available to the "insiders" for some time, but to the rest of us ordinary people (subscribers to Microsoft 365) quite recently.

Also try to acquaint yourself with the Dynamic Array functions. FILTER, SORT, UNIQUE, etc  A LOT of power in these.

JennyHoA20181 

Hi Jenny, I'm newer to answering questions here so sorry if my post is not as straight-forward as some of the other experts here. Generally speaking - I try to avoid nested IF statements as I feel like there's usually a more elegant solution, but considering your constraints on a number of a columns trying to receive max dates and return starting from the right most column I'm not sure how else I would do this.

I copied your table data from your post into a new excel sheet. I then had to normalize your data for myself. Your dates were not converting for me so I had to go back through and enter the date on top of it, that may have been from copy pasting, or regions as I'm US based so I handle dates as MM/DD/YYYY. Your records appeared to be DD/MM/YYYY however as I said my excel treated your columns as text instead of dates.

I also took a liberty of ignoring your 2019/2020 column constraint. It seemed to me that checking against that isn't going to add anything, if you want the status based on the max date on the record then there's no need to set a starting date range - you just want max date.

Before we get into formulas, I converted took your data range and made it a table with the table name Table1. This makes reading/writing formulas and accessing rows/columns easier. 

With that said here's what I did, first I want to get the max date from each record. This isn't too hard I inserted a column to the right of your "Stage" titled MaxDate. Getting that is as easy as using Max(Range). Using table and header references the formula becomes this: 

=Max(Table1[@[Date Confirmed Interest]:[Date Deferred]])

When using table references the @ symbol means "the row I'm in". So we can easily say give me the max date for the row I'm in for all my columns. Now that we have the maximum date that occurs, you wanted to get the right-most status that corresponds to that date. So this is where my nested IF comes in. Again there's probably a better way but this does work, if someone has a more elegant solution I'd be happy to learn that. In case you've not used IF's before they're an easy formula, it is basically IF("This is true","Give me what I want if true","Give me something else if false"). So now we say IF(DateDefferred=MaxDate,"Give me the header",IF(...))

So what we're doing is returning to ourselves the header information if it is the max date we're looking for, starting from our rightmost column, and then we nest another IF statement into the FALSE return. So if that column doesn't have the maxdate, we go onto the next column. We'll do this for each column until the end. At the end in the false area we're just going to put double quotes "" so that if every column is blank for some reason, it returns blank. This looks scary because it's a lot of text, but it's actually a very quick process. Using our table name as Table1 and referencing our columns here's what your formula starts to look like

=IF([@[Date Deferred]]=[@MaxDate],Table1[[#Headers],[Date Deferred]],
IF([@Date Closed]]=[@MaxDate],Table1[[#Headers],Date Closed]],
IF(...)))

You repeat until you have all your columns. But we're not done yet, because what we're doing right now is just returning the header. You don't want your statuses to read "Date Closed", you want it to read "Closed". Since each status starts with "Date" what we're going to do is just remove that from the start when we return our value. So now I'm going to wrap my IFs in a REPLACE function. When I'm wrapping formulas I like to write out in steps, so I do the entire IF above and then get that. And when I'm sure it's right and I like it, I add on my wrapper. Replace syntax is Replace(Text I'm replacing from,starting character,# of characters to replace,what to replace with). So we're going to say =Replace(Returned Header information,Start from 1st character,Replace 5 characters (date = 4 chars and you have a space, so that's 5),double quotes "" = blank)

Here's what it ends up looking like:

=REPLACE(IF([@[Date Deferred]]=[@MaxDate],Table1[[#Headers],[Date Deferred]],
IF([@Date Closed]]=[@MaxDate],Table1[[#Headers],Date Closed]],
IF(...))),1,5,"")

 Now we put it all together to create a hideously long formula that looks scary but definitely works:

=REPLACE(IF([@[Date Deferred]]=[@MaxDate],Table1[[#Headers],[Date Deferred]],
IF([@[Date Closed]]=[@MaxDate],Table1[[#Headers],[Date Closed]],
IF([@[Date Enrolled]]=[@MaxDate],Table1[[#Headers],[Date Enrolled]],
IF([@[Date Pre-Enrolled]]=[@MaxDate],Table1[[#Headers],[Date Pre-Enrolled]],
IF([@[Date Admitted]]=[@MaxDate],Table1[[#Headers],[Date Admitted]],
IF([@[Date Applied]]=[@MaxDate],Table1[[#Headers],[Date Applied]],
IF([@[Date Admissions File Pending]]=[@MaxDate],Table1[[#Headers],[Date Admissions File Pending]],
IF([@[Date Confirmed Interest]]=[@MaxDate],Table1[[#Headers],[Date Confirmed Interest]],"")))))))),1,5,"")

And now your stage column is populating with the exact stage based on the most recent event to occur for that record. If you want to get crazy, you can take the MaxDate formula and build that into the IF statements so you don't add a column at all, but I figured for learning purposes we'd skip that step. After doing all of that, here's what my table looked like:

Alright I think that's enough typing! Again sorry this is long, 2nd post ever, hoping to help where I can 🙂 Let me know if there are any questions.

-Dustin