How to pull in MAX Date based for "Charter Approved" and "Project Baselined" when they are 100%?

Jeffrey_PMO ✭✭✭✭
edited 10/05/21 in Formulas and Functions

Hey folks,

I'm looking to pull in the MAX Date for when:

• Charter Approved is Dated and 100% Complete

• Project Baselined is Dated and 100% Complete

For the screenshot below, the date pulled in should be 10/05/2021

I'm not sure if it matters but the formula is going into a Text cell.

Thank you!


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    You might run into a hiccup here because using the MAX formula for a date requires that the result be entered in a Date field. You can see that in the usage notes here:

    Usage Notes

    • When referencing dates, you must use MAX in a Date column. See Column Types for more information on Date columns.

    The formula you're looking for should be an IF/AND statement, I think. Something like:

    =IF(AND([Percent Complete]5 = 1, [Percent Complete]9 = 1), MAX([Start Date]:[Start Date]))

    Where 5 and 9 are the row numbers for the Charter Approved and Project Baseline rows respectively.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!