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 22-Nov-2021.

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 22-Nov-2021.

Hope this makes sense to someone!

Any help would be greatly appreciated.


Best Answer

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    edited 11/18/21 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,""))




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!