Get MAX date from 1 to many possible other dates that meet a criteria
I can't get my head around a solution for this formula, but I believe there's one in Smartsheet somewhere for it! Can you help?
I want to put the MAX date into the Actual Implementation Due Date column from a choice of 1 to 4 dates.
In this example, the formula should return 22Nov2021.
Implementation Due contains a date, which should always be considered.
There are 3 other columns (First Extension, Second Extension, Third Extension) that may be considered if a corresponding column is set to Implementation.
So, the formula should get a date from Implementation Due, First Extension (because First Extension Stage is set to Implementation), then it would look at and skip past Second Extension (because Second Extension Stage is set to Investigation), Third Extension (because Third Extension Stage is set to Implementation).
The formula would then return the MAX date from the 3 dates it found, returning 22Nov2021.
Hope this makes sense to someone!
Any help would be greatly appreciated.
Best Answer

Hi there,
Hope the below helps.
=max([implementation Due]@row, if([First Extension Stage]@row="Implementation",[First Extension]@row,""),if([Second Extension Stage]@row="Implementation",[Second Extension]@row,""),if([Third Extension Stage]@row="Implementation",[Third Extension]@row,""))
Cheers,
AJ.
Answers

Hi there,
Hope the below helps.
=max([implementation Due]@row, if([First Extension Stage]@row="Implementation",[First Extension]@row,""),if([Second Extension Stage]@row="Implementation",[Second Extension]@row,""),if([Third Extension Stage]@row="Implementation",[Third Extension]@row,""))
Cheers,
AJ.

Perfect! Thank you for this @BullandKhmer! Quite a simple formula in the end. I just couldn't see it.
Help Article Resources
Categories
Check out the Formula Handbook template!