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

%3CLINGO-SUB%20id%3D%22lingo-sub-1435973%22%20slang%3D%22en-US%22%3EChoose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435973%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I've%20been%20posting%20lots%20of%20excel%20questions%20regarding%20dates%20lately%2C%20thanks%20for%20your%20ongoing%20support!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20say%20what%20'Stage'%20an%20account%20was%2C%20at%20a%20certain%20point%20in%20time.%20For%20example%2C%20in%20table%20below%2C%20the%20Stage%20column%20(currently%20showing%20what%20result%20%3CSTRONG%3Eshould%3C%2FSTRONG%3E%20be)%2C%20needs%20to%20be%20a%20formula%20which%20shows%20the%20column%20name%20(minus%20the%20'Date'%20text)%2C%20that%20an%20account%20was%20at%20a%20point%20in%20time%20-%20the%20dates%20in%20the%202019%20or%202020%20columns.%20It%20should%20go%20by%20the%202019%20date%20if%20the%20Intake%20was%202019.2%20and%20the%202020%20column%20if%20the%20intake%20column%202020.2.%20Another%20thing%20to%20note%20is%20that%20the%20Date%20columns%20are%20in%20%3CSTRONG%3Echronological%20order%20-%20meaning%20if%20same%20date%20occurs%20in%202%20columns%2C%20pick%20the%20column%20furthest%20to%20right.%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3EHave%20also%20attached%20in%20excel%20in%20case.%20Look%20forward%20to%20responses!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221213%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%3EIntake%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Confirmed%20Interest%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Admissions%20File%20Pending%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Applied%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Admitted%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Pre-Enrolled%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Enrolled%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Closed%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EDate%20Deferred%3C%2FTD%3E%3CTD%20width%3D%22169%22%3EStage%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F05%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EClosed%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E03%2F06%2F2019%3C%2FTD%3E%3CTD%3E03%2F06%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F05%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EClosed%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E11%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E12%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdmissions%20File%20Pending%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%3EDeferred%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E24%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E02%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E03%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E03%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPre-enrolled%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E10%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E11%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdmissions%20File%20Pending%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E27%2F05%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EClosed%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E04%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E05%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E14%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E08%2F07%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdmissions%20File%20Pending%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E17%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E17%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E22%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E22%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E20%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPre-enrolled%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E31%2F05%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E01%2F08%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E02%2F08%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EConfirmed%20Interest%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E20%2F05%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EClosed%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E11%2F06%2F2019%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%22blank%22%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E21%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E24%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E26%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E19%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPre-enrolled%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1435973%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436052%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20been%20involved%20(or%20my%20memory%20fails%20me)%20in%20any%20of%20your%20prior%20date-related%20questions.%20And%20I'm%20definitely%20not%20at%20the%20expertise%20level%20of%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20but...all%20those%20disclaimers%20aside%2C%20when%20I%20look%20at%20your%20spreadsheet%20it%20strikes%20me%20as%20a%20strange%20design.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20that%20I%20mean%2C%20something%20seems%20to%20be%20missing%3A%20it%20refers%20to%20people%2C%20right%3F%20To%20stages%20of%20processing%20an%20application%20for%20something%20or%20other%3F%20Where%20is%20the%20ID%20(yes%2C%20it%20has%20to%20be%20anonymous%20here%20on%20this%20website)%3F%20So%20I%20assume%20you've%20either%20left%20it%20off%20intentionally%2C%20or%20there's%20something%20else%20going%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20all%20that%20said%2C%20I%20wonder%20why%20you%20don't%20create%20a%20table%20that%20has%3C%2FP%3E%3CUL%3E%3CLI%3Ea%20column%20for%20a%20unique%20identifier%20for%20each%20person%20(student%20ID%2C%20ssn%2C%20whatever...some%20unique%20ID)%3C%2FLI%3E%3CLI%3EDate%20(of%20action%20or%20status)%2C%3C%2FLI%3E%3CLI%3EStatus%20Code%20(to%20correspond%20to%20those%20Stages%20across%20the%20top%20of%20your%20sheet)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EFrom%20a%20table%20like%20that%20at%20the%20INPUT%20end%20of%20things%2C%20you'd%20be%20able%20quite%20readily%20to%20generate%20an%20OUTPUT%20report%20that%20resembles%20your%20current%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20short%2C%20what%20you%20have%20appears%20to%20me%20at%20any%20rate%20(and%20I%20could%20be%20wrong)%20as%20an%20output%20or%20report%2C%20in%20which%20you're%20requiring%20that%20you%20(or%20some%20other%20user)%20do%20the%20%22heavy%20lifting%22%20of%20figuring%20out%20which%20row%20and%20column%20to%20add%20a%20new%20date%20to%20in%20order%20to%20track%20the%20history%20of%20somebody.%20You%20may%2C%20I%20realize%2C%20be%20asking%20that%20Excel%20do%20the%20relatively%20trivial%20work%20of%20filling%20in%20your%20last%20column%20(getting%20the%20max%20date%20and%20giving%20a%20Stage%20description).%20%3CU%3E%3CEM%3EBut%20in%20fact%2C%20THIS%20is%20precisely%20the%20kind%20of%20output%2Freport%20that%20Excel%20excels%20in%20producing%20from%20a%20simple%20database%20or%20Excel%20Table%20such%20as%20I%20describe%20above.%3C%2FEM%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20my%20question%2C%20as%20we%20await%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Sergei%20Baklan%3C%2FA%3E's%20input%2C%20is%20whether%20or%20not%20you'd%20be%20open%20to%20a%20different%20way%20of%20tracking%20this%20history%20and%20reporting%20on%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436093%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436093%22%20slang%3D%22en-US%22%3EThanks!%20Yes%20I've%20left%20the%20applicant%20ID%20out.%20Until%20we%20get%20the%20developments%20I%20need%20built%20in%20the%20CRM%20%2F%20database%2C%20it%20has%20to%20be%20done%20in%20excel%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436430%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jenny%2C%20I'm%20newer%20to%20answering%20questions%20here%20so%20sorry%20if%20my%20post%20is%20not%20as%20straight-forward%20as%20some%20of%20the%20other%20experts%20here.%20Generally%20speaking%20-%20I%20try%20to%20avoid%20nested%20IF%20statements%20as%20I%20feel%20like%20there's%20usually%20a%20more%20elegant%20solution%2C%20but%20considering%20your%20constraints%20on%20a%20number%20of%20a%20columns%20trying%20to%20receive%20max%20dates%20and%20return%20starting%20from%20the%20right%20most%20column%20I'm%20not%20sure%20how%20else%20I%20would%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20copied%20your%20table%20data%20from%20your%20post%20into%20a%20new%20excel%20sheet.%20I%20then%20had%20to%20normalize%20your%20data%20for%20myself.%20Your%20dates%20were%20not%20converting%20for%20me%20so%20I%20had%20to%20go%20back%20through%20and%20enter%20the%20date%20on%20top%20of%20it%2C%20that%20may%20have%20been%20from%20copy%20pasting%2C%20or%20regions%20as%20I'm%20US%20based%20so%20I%20handle%20dates%20as%20MM%2FDD%2FYYYY.%20Your%20records%20appeared%20to%20be%20DD%2FMM%2FYYYY%20however%20as%20I%20said%20my%20excel%20treated%20your%20columns%20as%20text%20instead%20of%20dates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20took%20a%20liberty%20of%20ignoring%20your%202019%2F2020%20column%20constraint.%20It%20seemed%20to%20me%20that%20checking%20against%20that%20isn't%20going%20to%20add%20anything%2C%20if%20you%20want%20the%20status%20based%20on%20the%20max%20date%20on%20the%20record%20then%20there's%20no%20need%20to%20set%20a%20starting%20date%20range%20-%20you%20just%20want%20max%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20we%20get%20into%20formulas%2C%20I%20converted%20took%20your%20data%20range%20and%20made%20it%20a%20table%20with%20the%20table%20name%20Table1.%20This%20makes%20reading%2Fwriting%20formulas%20and%20accessing%20rows%2Fcolumns%20easier.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20that%20said%20here's%20what%20I%20did%2C%20first%20I%20want%20to%20get%20the%20max%20date%20from%20each%20record.%20This%20isn't%20too%20hard%20I%20inserted%20a%20column%20to%20the%20right%20of%20your%20%22Stage%22%20titled%20MaxDate.%20Getting%20that%20is%20as%20easy%20as%20using%20Max(Range).%20Using%20table%20and%20header%20references%20the%20formula%20becomes%20this%3A%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DMax(Table1%5B%40%5BDate%20Confirmed%20Interest%5D%3A%5BDate%20Deferred%5D%5D)%3C%2FPRE%3E%3CP%3EWhen%20using%20table%20references%20the%26nbsp%3B%40%20symbol%20means%20%22the%20row%20I'm%20in%22.%20So%20we%20can%20easily%20say%20give%20me%20the%20max%20date%20for%20the%20row%20I'm%20in%20for%20all%20my%20columns.%26nbsp%3BNow%20that%20we%20have%20the%20maximum%20date%20that%20occurs%2C%20you%20wanted%20to%20get%20the%20right-most%20status%20that%20corresponds%20to%20that%20date.%20So%20this%20is%20where%20my%20nested%20IF%20comes%20in.%20Again%20there's%20probably%20a%20better%20way%20but%20this%20does%20work%2C%20if%20someone%20has%20a%20more%20elegant%20solution%20I'd%20be%20happy%20to%20learn%20that.%20In%20case%20you've%20not%20used%20IF's%20before%20they're%20an%20easy%20formula%2C%20it%20is%20basically%20IF(%22This%20is%20true%22%2C%22Give%20me%20what%20I%20want%20if%20true%22%2C%22Give%20me%20something%20else%20if%20false%22).%20So%20now%20we%20say%20IF(DateDefferred%3DMaxDate%2C%22Give%20me%20the%20header%22%2CIF(...))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20we're%20doing%20is%20returning%20to%20ourselves%20the%20header%20information%20if%20it%20is%20the%20max%20date%20we're%20looking%20for%2C%20starting%20from%20our%20rightmost%20column%2C%20and%20then%20we%20nest%20another%20IF%20statement%20into%20the%20FALSE%20return.%20So%20if%20that%20column%20doesn't%20have%20the%20maxdate%2C%20we%20go%20onto%20the%20next%20column.%20We'll%20do%20this%20for%20each%20column%20until%20the%20end.%20At%20the%20end%20in%20the%20false%20area%20we're%20just%20going%20to%20put%20double%20quotes%20%22%22%20so%20that%20if%20every%20column%20is%20blank%20for%20some%20reason%2C%20it%20returns%20blank.%20This%20looks%20scary%20because%20it's%20a%20lot%20of%20text%2C%20but%20it's%20actually%20a%20very%20quick%20process.%20Using%20our%20table%20name%20as%20Table1%20and%20referencing%20our%20columns%20here's%20what%20your%20formula%20starts%20to%20look%20like%3C%2FP%3E%3CPRE%3E%3DIF(%5B%40%5BDate%20Deferred%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Deferred%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40Date%20Closed%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2CDate%20Closed%5D%5D%2C%3CBR%20%2F%3EIF(...)))%3C%2FPRE%3E%3CP%3EYou%20repeat%20until%20you%20have%20all%20your%20columns.%20But%20we're%20not%20done%20yet%2C%20because%20what%20we're%20doing%20right%20now%20is%20just%20returning%20the%20header.%20You%20don't%20want%20your%20statuses%20to%20read%20%22Date%20Closed%22%2C%20you%20want%20it%20to%20read%20%22Closed%22.%20Since%20each%20status%20starts%20with%20%22Date%22%20what%20we're%20going%20to%20do%20is%20just%20remove%20that%20from%20the%20start%20when%20we%20return%20our%20value.%20So%20now%20I'm%20going%20to%20wrap%20my%20IFs%20in%20a%20REPLACE%20function.%20When%20I'm%20wrapping%20formulas%20I%20like%20to%20write%20out%20in%20steps%2C%20so%20I%20do%20the%20entire%20IF%20above%20and%20then%20get%20that.%20And%20when%20I'm%20sure%20it's%20right%20and%20I%20like%20it%2C%20I%20add%20on%20my%20wrapper.%20Replace%20syntax%20is%20Replace(Text%20I'm%20replacing%20from%2Cstarting%20character%2C%23%20of%20characters%20to%20replace%2Cwhat%20to%20replace%20with).%20So%20we're%20going%20to%20say%20%3DReplace(Returned%20Header%20information%2CStart%20from%201st%20character%2CReplace%205%20characters%20(date%20%3D%204%20chars%20and%20you%20have%20a%20space%2C%20so%20that's%205)%2Cdouble%20quotes%20%22%22%20%3D%20blank)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20it%20ends%20up%20looking%20like%3A%3C%2FP%3E%3CPRE%3E%3DREPLACE(IF(%5B%40%5BDate%20Deferred%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Deferred%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40Date%20Closed%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2CDate%20Closed%5D%5D%2C%3CBR%20%2F%3EIF(...)))%2C1%2C5%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3BNow%20we%20put%20it%20all%20together%20to%20create%20a%20hideously%20long%20formula%20that%20looks%20scary%20but%20definitely%20works%3A%3C%2FP%3E%3CPRE%3E%3DREPLACE(IF(%5B%40%5BDate%20Deferred%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Deferred%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Closed%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Closed%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Enrolled%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Enrolled%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Pre-Enrolled%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Pre-Enrolled%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Admitted%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Admitted%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Applied%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Applied%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Admissions%20File%20Pending%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Admissions%20File%20Pending%5D%5D%2C%3CBR%20%2F%3EIF(%5B%40%5BDate%20Confirmed%20Interest%5D%5D%3D%5B%40MaxDate%5D%2CTable1%5B%5B%23Headers%5D%2C%5BDate%20Confirmed%20Interest%5D%5D%2C%22%22))))))))%2C1%2C5%2C%22%22)%3C%2FPRE%3E%3CP%3EAnd%20now%20your%20stage%20column%20is%20populating%20with%20the%20exact%20stage%20based%20on%20the%20most%20recent%20event%20to%20occur%20for%20that%20record.%20If%20you%20want%20to%20get%20crazy%2C%20you%20can%20take%20the%20MaxDate%20formula%20and%20build%20that%20into%20the%20IF%20statements%20so%20you%20don't%20add%20a%20column%20at%20all%2C%20but%20I%20figured%20for%20learning%20purposes%20we'd%20skip%20that%20step.%20After%20doing%20all%20of%20that%2C%20here's%20what%20my%20table%20looked%20like%3A%3C%2FP%3E%3CTABLE%20width%3D%221557px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3EIntake%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3EDate%20Confirmed%20Interest%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3EDate%20Admissions%20File%20Pending%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3EDate%20Applied%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3EDate%20Admitted%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3EDate%20Pre-Enrolled%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3EDate%20Enrolled%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3EDate%20Closed%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3EDate%20Deferred%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EState%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EMaxDate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E5%2F26%2F2020%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EClosed%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E5%2F26%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E5%2F26%2F2020%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EClosed%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E5%2F26%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E6%2F11%2F2019%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E6%2F12%2F2019%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E6%2F26%2F2019%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E6%2F26%2F2019%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EEnrolled%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E6%2F26%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EDeferred%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E2%2F24%2F2020%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E2%2F3%2F2020%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E3%2F3%2F2020%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E3%2F3%2F2020%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E3%2F26%2F2020%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EPre-Enrolled%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E3%2F26%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E10%2F6%2F2019%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E11%2F6%2F2019%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E6%2F26%2F2019%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E6%2F26%2F2019%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EAdmitted%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E11%2F6%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E5%2F27%2F2020%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EClosed%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E5%2F27%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E4%2F6%2F2019%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E5%2F6%2F2019%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E6%2F14%2F2019%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E8%2F7%2F2019%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EEnrolled%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E8%2F7%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E1%2F17%2F2020%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E7%2F17%2F2019%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E1%2F22%2F2020%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E1%2F22%2F2020%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E2%2F20%2F2020%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EPre-Enrolled%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E2%2F20%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E1%2F8%2F2019%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E2%2F8%2F2019%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EConfirmed%20Interest%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E5%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E5%2F20%2F2020%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EClosed%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E5%2F20%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2019.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E6%2F11%2F2019%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EClosed%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E6%2F11%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2266px%22%20height%3D%2230px%22%3E2020.2%3C%2FTD%3E%3CTD%20width%3D%22200px%22%20height%3D%2230px%22%3E1%2F21%2F2020%3C%2FTD%3E%3CTD%20width%3D%22247px%22%20height%3D%2230px%22%3E2%2F24%2F2020%3C%2FTD%3E%3CTD%20width%3D%22119px%22%20height%3D%2230px%22%3E2%2F26%2F2020%3C%2FTD%3E%3CTD%20width%3D%22129px%22%20height%3D%2230px%22%3E2%2F26%2F2020%3C%2FTD%3E%3CTD%20width%3D%22155px%22%20height%3D%2230px%22%3E3%2F19%2F2020%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186px%22%20height%3D%2230px%22%3EPre-Enrolled%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E3%2F19%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlright%20I%20think%20that's%20enough%20typing!%20Again%20sorry%20this%20is%20long%2C%202nd%20post%20ever%2C%20hoping%20to%20help%20where%20I%20can%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Let%20me%20know%20if%20there%20are%20any%20questions.%3C%2FP%3E%3CP%3E-Dustin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436636%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20I%20think%20works%2C%20except%20for%20your%20column%20%22Admissions%20File%20Pending%22%20which%20you%20give%20as%20the%20%22Stage%22%20for%20a%20couple%20of%20entries%2C%20even%20though%20it's%20NOT%20the%20max%20date%20in%20the%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20I've%20done%20it%20as%20three%20separate%20formulas%20first%2C%20and%20then%20a%20single%20nested%20monstrosity.%20First%20the%20three%20separate%20formulas%3A%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3E%3DMAX(B2%3AI2)%3C%2FSTRONG%3E%26nbsp%3B%20gets%20the%20max%20date%20in%20a%20row%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3DXMATCH(K2%2CB2%3AI2%2C0%2C-1)%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3B%20gets%20the%20right%20most%20column%20number%20for%20that%20max%20date%20(the%20%22-1%22%20goes%20from%20right%20to%20left%20through%20the%20range%20to%20get%20that%20match)%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3DCHOOSE(L2%2C%22Confirmed%20Interest%22%2C%22Admissions%20File%20Pending%22%2C%22Applied%22%2C%22Admitted%22%2C%22Pre-Enrolled%22%2C%22Enrolled%22%2C%22Closed%22%2C%22Deferred%22)%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3B%20takes%20that%20right%20most%20column%20number%20and%20supplies%20the%20corresponding%20words%20from%20the%20header%20row.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENested%2C%20they%20look%20like%20this%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E%3DCHOOSE(XMATCH(MAX(B2%3AI2)%2CB2%3AI2%2C0%2C-1)%2C%22Confirmed%20Interest%22%2C%22Admissions%20File%20Pending%22%2C%22Applied%22%2C%22Admitted%22%2C%22Pre-Enrolled%22%2C%22Enrolled%22%2C%22Closed%22%2C%22Deferred%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20doesn't%20work%20for%20you%2C%20let%20me%20know%20where%20or%20how%20it's%20failing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20It%20was%20fun%3A%20first%20time%20I've%20tried%20to%20use%20%3CSTRONG%3EXMATCH%3C%2FSTRONG%3E...%26nbsp%3B%20turned%20out%20to%20be%20very%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436750%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436750%22%20slang%3D%22en-US%22%3EHey%2C%20thanks%20for%20this!%20Ok%20close!%20Just%20one%20thing!%3CBR%20%2F%3E%3CBR%20%2F%3E%22except%20for%20your%20column%20%22Admissions%20File%20Pending%22%20which%20you%20give%20as%20the%20%22Stage%22%20for%20a%20couple%20of%20entries%2C%20even%20though%20it's%20NOT%20the%20max%20date%20in%20the%20row.%22%20-%20that's%20because%20i%20want%20to%20know%20the%20stage%20as%20of%2003%2F06%2F2019%20for%20rows%20where%20intake%20%3D%202019.2%20and%20as%20of%2003%2F06%2F2020%20for%20rows%20where%20intake%20%3D%202020.2%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20help!%20never%20heard%20of%20an%20Xmatch%20before!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1437647%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1437647%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687774%22%20target%3D%22_blank%22%3E%40DustinDoucette%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20mind%20uploading%20the%20excel%20with%20the%20formula%20inside%20it%2C%20as%20when%20I%20add%20the%20formula%20to%20my%20excel%2C%20it%20doesn't%20seem%20to%20work%3F%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1437799%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1437799%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3BHi%20Jenny%2C%20I've%20attached%20the%20book%20with%20the%20formula%20in%20it.%20Note%20that%20I%20had%20converted%20your%20date%20format%20to%20MM%2FDD%2FYYYY%20so%20if%20you%20want%20it%20to%20read%20your%20way%20you'll%20probably%20have%20to%20just%20format%20the%20date%20columns%20again%2C%20but%20otherwise%20I%20think%20this%20should%20do%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1437806%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1437806%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EXMATCH%3C%2FSTRONG%3E%20(and%20%3CSTRONG%3EXLOOKUP%3C%2FSTRONG%3E)%20are%20quite%20new.%20They've%20been%20available%20to%20the%20%22insiders%22%20for%20some%20time%2C%20but%20to%20the%20rest%20of%20us%20ordinary%20people%20(subscribers%20to%20Microsoft%20365)%20quite%20recently.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20try%20to%20acquaint%20yourself%20with%20the%20Dynamic%20Array%20functions.%20%3CSTRONG%3EFILTER%2C%20SORT%2C%20UNIQUE%3C%2FSTRONG%3E%2C%20etc%26nbsp%3B%20A%20LOT%20of%20power%20in%20these.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436960%22%20slang%3D%22en-US%22%3ERe%3A%20Choose%20MAX%20date%20from%20several%20columns%2C%20based%20on%20TODAY's%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436960%22%20slang%3D%22en-US%22%3ENow%20understanding%20what%20that%20is%2C%20here's%20a%20way%20to%20get%20Max%20Date%20dependent%20on%20the%202019.2%20or%202020.2%20reference%3A%3CBR%20%2F%3E%3DMAXIFS(B2%3AI2%2CB2%3AI2%2C%22%26lt%3B%22%26amp%3BIF(A2%3D%24M%241%2C%24M%242%2C%24N%242))%3CBR%20%2F%3E%3CBR%20%2F%3EOnly%20one%20tweak%20to%20make%20it%20work%20easily%2C%20update%20your%20M1%20and%20N1%20headers%20to%20match%20your%20intake%20column%2C%20so%202019%20will%20become%202019.2%20and%202020.2.%20Now%20this%20way%20you%20can%20check%20if%20intake%20matches%2C%20use%20that%20date%20below.%20I%20updated%20Mathetes%20formula%20and%20it%20worked%20perfectly%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCHOOSE(XMATCH(MAXIFS(B2%3AI2%2CB2%3AI2%2C%22%26lt%3B%22%26amp%3BIF(A2%3D%24M%241%2C%24M%242%2C%24N%242))%2CB2%3AI2%2C0%2C-1)%2C%22Confirmed%20Interest%22%2C%22Admissions%20File%20Pending%22%2C%22Applied%22%2C%22Admitted%22%2C%22Pre-Enrolled%22%2C%22Enrolled%22%2C%22Closed%22%2C%22Deferred%22)%3CBR%20%2F%3E%3CBR%20%2F%3EStill%20much%20more%20elegant%20than%20my%20nested%20IFS!%3C%2FLINGO-BODY%3E
Contributor

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 

 

IntakeDate Confirmed InterestDate Admissions File PendingDate AppliedDate AdmittedDate Pre-EnrolledDate EnrolledDate ClosedDate DeferredStage 20192020
2020.2      26/05/2020 Closed 03/06/201903/06/2020
2020.2      26/05/2020 Closed   
2019.231/05/201931/05/201911/06/201912/06/201926/06/201926/06/2019  Admissions File Pending   
2019.231/05/2019     31/05/201931/05/2019Deferred   
2020.224/02/202002/03/202003/03/202003/03/202026/03/2020   Pre-enrolled   
2019.231/05/201931/05/201910/06/201911/06/201926/06/201926/06/2019  Admissions File Pending   
2020.2      27/05/2020 Closed   
2019.231/05/201931/05/201904/06/201905/06/201914/06/201908/07/2019  Admissions File Pending   
2020.217/01/202017/01/202022/01/202022/01/202020/02/2020   Pre-enrolled   
2019.231/05/201901/08/2019    02/08/2019 Confirmed Interest   
2020.2      20/05/2020 Closed   
2019.2      11/06/2019 "blank"   
2020.221/01/202024/02/202026/02/202026/02/202019/03/2020   Pre-enrolled   
9 Replies

@JennySommet 

 

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

  • a column for a unique identifier for each person (student ID, ssn, whatever...some unique ID)
  • Date (of action or status),
  • Status Code (to correspond to those Stages across the top of your sheet)

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?

Thanks! Yes I've left the applicant ID out. Until we get the developments I need built in the CRM / database, it has to be done in excel

@JennySommet 

 

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:

IntakeDate Confirmed InterestDate Admissions File PendingDate AppliedDate AdmittedDate Pre-EnrolledDate EnrolledDate ClosedDate DeferredStateMaxDate
2020.2      5/26/2020 Closed5/26/2020
2020.2      5/26/2020 Closed5/26/2020
2019.25/31/20195/31/20196/11/20196/12/20196/26/20196/26/2019  Enrolled6/26/2019
2019.25/31/2019     5/31/20195/31/2019Deferred5/31/2019
2020.22/24/20202/3/20203/3/20203/3/20203/26/2020   Pre-Enrolled3/26/2020
2019.25/31/20195/31/201910/6/201911/6/20196/26/20196/26/2019  Admitted11/6/2019
2020.2      5/27/2020 Closed5/27/2020
2019.25/31/20195/31/20194/6/20195/6/20196/14/20198/7/2019  Enrolled8/7/2019
2020.21/17/20207/17/20191/22/20201/22/20202/20/2020   Pre-Enrolled2/20/2020
2019.25/31/20191/8/2019    2/8/2019 Confirmed Interest5/31/2019
2020.2      5/20/2020 Closed5/20/2020
2019.2      6/11/2019 Closed6/11/2019
2020.21/21/20202/24/20202/26/20202/26/20203/19/2020   Pre-Enrolled3/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

@JennySommet 

 

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:

  • =MAX(B2:I2)  gets the max date in a row
  • =XMATCH(K2,B2:I2,0,-1)   gets the right most column number for that max date (the "-1" goes from right to left through the range to get that match)
  • =CHOOSE(L2,"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred")   takes that right most column number and supplies the corresponding words from the header row.

 

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.

Hey, thanks for this! Ok close! 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

Thanks for your help! never heard of an Xmatch before!

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!

 

Hi @DustinDoucette 

 

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!

 

@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!

@JennySommet 

 

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.