Formula Help Please!

Jim Schaffhausen
Jim Schaffhausen ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi Community-

I have a column that I want to say what phase a project is in based upon the criteria below

4 Possible Phases – “Strategy & Intake”, “Brief Finalization”, “Detailed Concepts”, “Production to Market”

#1 “Strategy & Intake” – Milestone “BRIEF COMPLETED & APPROVED” is not checked and is not in the next 21 days or more.

#2 “Brief Finalization” – Milestone “BRIEF COMPLETED & APPROVED” is not checked and is in the next 20 days

#3 “Detailed Concepts” – Milestone “BRIEF COMPLETED & APPROVED” is checked

#4 “Production to Market” – Milestone “APPROVED DETAILED CONCEPTS” is checked

Any help using the @row would be much appreciated!




  • Frank Falco
    Frank Falco ✭✭✭✭✭✭

    You need to consider the order of evaluation. In this case, this is the order I would use:

    #4 - If Approved Detailed Concepts is checked, then nothing more to check

    #3 - If both Brief Completed & Approved is checked then nothing more to check

    Check that Milestone is a Date otherwise, cannot evaluate days ahead

    #2 - Brief Completed & Approved is not checked & in next 20 days <=Today(20)

    #1 - Brief Completed & Approved is not checked & 21 days  or more >TODAY(20)

    Use @row in place of row numbers so speeds up calc and reordering.

    Assuming Brief Completed, Approved, Approved Detailed Concepts are all individual checkbox columns, and Milestone is a date column, try this code in the Phases column.

    =IF([Approved Detailed Concepts]@row=1,"Production to Market",

    IF(AND([Brief Completed]@row=1,Approved@row=1),"Detailed Concepts",

    IF(AND(ISDATE(Milestone@row), [Brief Completed]@row=0,Approved@row=0),

    IF(Milestone@row<=TODAY(20), "Brief Finalization", "Strategy & Intake"))))

    It is a little ugly but should work.

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are all of the milestones in the same column, or are they column headers with dates/checks going across a row?

  • Jim Schaffhausen
    Jim Schaffhausen ✭✭✭✭

    Hi Paul-

    They are going across a row.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this a try...


    =IF([Approved Detailed Concepts]@row = 1, "Production to Market", IF([Brief Completed & Approved]@row = 1, "Detailed Concepts", IF([Brief Completed & Approved (date)] >= TODAY(-20), "Brief Finalization", "Strategy & Intake")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!