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.

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

BullandKhmer

Hi there,

Hope the below helps.

Cheers,

AJ.

John Renney

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.