Jun 03 2020 05:36 AM
Hi everyone,
I know I've been posting lots of excel questions regarding dates lately, thanks for your ongoing support!
I need to say what 'Stage' an account was, at a certain point in time. For example, in table below, the Stage column (currently showing what result should be), needs to be a formula which shows the column name (minus the 'Date' text), that an account was at a point in time - the dates in the 2019 or 2020 columns. It should go by the 2019 date if the Intake was 2019.2 and the 2020 column if the intake column 2020.2. Another thing to note is that the Date columns are in chronological order - meaning if same date occurs in 2 columns, pick the column furthest to right. Have also attached in excel in case. Look forward to responses! @Sergei Baklan
Intake | Date Confirmed Interest | Date Admissions File Pending | Date Applied | Date Admitted | Date Pre-Enrolled | Date Enrolled | Date Closed | Date Deferred | Stage | 2019 | 2020 | |
2020.2 | 26/05/2020 | Closed | 03/06/2019 | 03/06/2020 | ||||||||
2020.2 | 26/05/2020 | Closed | ||||||||||
2019.2 | 31/05/2019 | 31/05/2019 | 11/06/2019 | 12/06/2019 | 26/06/2019 | 26/06/2019 | Admissions File Pending | |||||
2019.2 | 31/05/2019 | 31/05/2019 | 31/05/2019 | Deferred | ||||||||
2020.2 | 24/02/2020 | 02/03/2020 | 03/03/2020 | 03/03/2020 | 26/03/2020 | Pre-enrolled | ||||||
2019.2 | 31/05/2019 | 31/05/2019 | 10/06/2019 | 11/06/2019 | 26/06/2019 | 26/06/2019 | Admissions File Pending | |||||
2020.2 | 27/05/2020 | Closed | ||||||||||
2019.2 | 31/05/2019 | 31/05/2019 | 04/06/2019 | 05/06/2019 | 14/06/2019 | 08/07/2019 | Admissions File Pending | |||||
2020.2 | 17/01/2020 | 17/01/2020 | 22/01/2020 | 22/01/2020 | 20/02/2020 | Pre-enrolled | ||||||
2019.2 | 31/05/2019 | 01/08/2019 | 02/08/2019 | Confirmed Interest | ||||||||
2020.2 | 20/05/2020 | Closed | ||||||||||
2019.2 | 11/06/2019 | "blank" | ||||||||||
2020.2 | 21/01/2020 | 24/02/2020 | 26/02/2020 | 26/02/2020 | 19/03/2020 | Pre-enrolled |
Jun 03 2020 05:58 AM
I haven't been involved (or my memory fails me) in any of your prior date-related questions. And I'm definitely not at the expertise level of @Sergei Baklan, but...all those disclaimers aside, when I look at your spreadsheet it strikes me as a strange design.
By that I mean, something seems to be missing: it refers to people, right? To stages of processing an application for something or other? Where is the ID (yes, it has to be anonymous here on this website)? So I assume you've either left it off intentionally, or there's something else going on.
And all that said, I wonder why you don't create a table that has
From a table like that at the INPUT end of things, you'd be able quite readily to generate an OUTPUT report that resembles your current spreadsheet.
In short, what you have appears to me at any rate (and I could be wrong) as an output or report, in which you're requiring that you (or some other user) do the "heavy lifting" of figuring out which row and column to add a new date to in order to track the history of somebody. You may, I realize, be asking that Excel do the relatively trivial work of filling in your last column (getting the max date and giving a Stage description). But in fact, THIS is precisely the kind of output/report that Excel excels in producing from a simple database or Excel Table such as I describe above.
So my question, as we await @Sergei Baklan's input, is whether or not you'd be open to a different way of tracking this history and reporting on it?
Jun 03 2020 06:06 AM
Jun 03 2020 07:48 AM
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:
Intake | Date Confirmed Interest | Date Admissions File Pending | Date Applied | Date Admitted | Date Pre-Enrolled | Date Enrolled | Date Closed | Date Deferred | State | MaxDate |
2020.2 | 5/26/2020 | Closed | 5/26/2020 | |||||||
2020.2 | 5/26/2020 | Closed | 5/26/2020 | |||||||
2019.2 | 5/31/2019 | 5/31/2019 | 6/11/2019 | 6/12/2019 | 6/26/2019 | 6/26/2019 | Enrolled | 6/26/2019 | ||
2019.2 | 5/31/2019 | 5/31/2019 | 5/31/2019 | Deferred | 5/31/2019 | |||||
2020.2 | 2/24/2020 | 2/3/2020 | 3/3/2020 | 3/3/2020 | 3/26/2020 | Pre-Enrolled | 3/26/2020 | |||
2019.2 | 5/31/2019 | 5/31/2019 | 10/6/2019 | 11/6/2019 | 6/26/2019 | 6/26/2019 | Admitted | 11/6/2019 | ||
2020.2 | 5/27/2020 | Closed | 5/27/2020 | |||||||
2019.2 | 5/31/2019 | 5/31/2019 | 4/6/2019 | 5/6/2019 | 6/14/2019 | 8/7/2019 | Enrolled | 8/7/2019 | ||
2020.2 | 1/17/2020 | 7/17/2019 | 1/22/2020 | 1/22/2020 | 2/20/2020 | Pre-Enrolled | 2/20/2020 | |||
2019.2 | 5/31/2019 | 1/8/2019 | 2/8/2019 | Confirmed Interest | 5/31/2019 | |||||
2020.2 | 5/20/2020 | Closed | 5/20/2020 | |||||||
2019.2 | 6/11/2019 | Closed | 6/11/2019 | |||||||
2020.2 | 1/21/2020 | 2/24/2020 | 2/26/2020 | 2/26/2020 | 3/19/2020 | Pre-Enrolled | 3/19/2020 |
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
Jun 03 2020 08:43 AM - edited Jun 03 2020 08:46 AM
Here's what I think works, except for your column "Admissions File Pending" which you give as the "Stage" for a couple of entries, even though it's NOT the max date in the row.
Anyway, I've done it as three separate formulas first, and then a single nested monstrosity. First the three separate formulas:
Nested, they look like this
=CHOOSE(XMATCH(MAX(B2:I2),B2:I2,0,-1),"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred")
If this doesn't work for you, let me know where or how it's failing.
P.S. It was fun: first time I've tried to use XMATCH... turned out to be very useful.
Jun 03 2020 08:58 AM
Jun 03 2020 09:17 AM
Jun 03 2020 10:11 AM
Would you mind uploading the excel with the formula inside it, as when I add the formula to my excel, it doesn't seem to work? Thanks!
Jun 03 2020 10:34 AM
@JennySommet Hi Jenny, I've attached the book with the formula in it. Note that I had converted your date format to MM/DD/YYYY so if you want it to read your way you'll probably have to just format the date columns again, but otherwise I think this should do it!
Jun 03 2020 10:35 AM
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.