Forum Discussion
Choose MAX date from several columns, based on TODAY's DATE
Hi everyone, Outcome is to report which '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 ...
Just one thing!
"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." - that's because i want to know the stage as of 03/06/2019 for rows where intake = 2019.2 and as of 03/06/2020 for rows where intake = 2020.2
Now understanding what that is, here's a way to get Max Date dependent on the 2019.2 or 2020.2 reference:
=MAXIFS(B2:I2,B2:I2,"<"&IF(A2=$M$1,$M$2,$N$2))
Only one tweak to make it work easily, update your M1 and N1 headers to match your intake column, so 2019 will become 2019.2 and 2020.2. Now this way you can check if intake matches, use that date below. I updated Mathetes formula and it worked perfectly:
=CHOOSE(XMATCH(MAXIFS(B2:I2,B2:I2,"<"&IF(A2=$M$1,$M$2,$N$2)),B2:I2,0,-1),"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred")
Still much more elegant than my nested IFS!
=MAXIFS(B2:I2,B2:I2,"<"&IF(A2=$M$1,$M$2,$N$2))
Only one tweak to make it work easily, update your M1 and N1 headers to match your intake column, so 2019 will become 2019.2 and 2020.2. Now this way you can check if intake matches, use that date below. I updated Mathetes formula and it worked perfectly:
=CHOOSE(XMATCH(MAXIFS(B2:I2,B2:I2,"<"&IF(A2=$M$1,$M$2,$N$2)),B2:I2,0,-1),"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred")
Still much more elegant than my nested IFS!
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!
JennyHoA20181 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!