Get MAX date from 1 to many possible other dates that meet a criteria

John Renney

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.

BullandKhmer

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.

BullandKhmer

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.

John Renney

Perfect! Thank you for this @BullandKhmer! Quite a simple formula in the end. I just couldn't see it.