Formula for Status

danf76
danf76
edited 01/31/21 in Formulas and Functions

I would like to auto create a status based on an end date, completed date, cancelled checkbox or on hold checkbox. If end date is in the future an neither checkbox is selected then "Open". If the end date is in the past then "Overdue". If complete date has been entered then "Complete". If cancelled checked then "Cancelled". If On Hold checked then "On Hold". This is what I have so far with no luck.

=IF(ISDATE([Complete Date]@row), "Complete", IF([due due]@row > today (), "Complete", IF(Cancelled@row = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @danf76

    Try this

    =IF(ISDATE([Complete Date]@row), "Complete", IF(TODAY()>[End Date]@row, "Overdue", IF(Cancelled@row = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

    The second term seemed it might be mistaken? I didn't include it in this formula.

    IF([due due]@row > today (), "Complete" (If correct, it should be written as IF([due due]@row> TODAY(), "Complete" )

    Please note that column name references are both spelling and case-sensitive.

    Please advise on the second term

    cheers,

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @danf76

    Try this

    =IF(ISDATE([Complete Date]@row), "Complete", IF(TODAY()>[End Date]@row, "Overdue", IF(Cancelled@row = 1, "Cancelled", IF([On Hold]@row = 1, "On Hold", "Open"))))

    The second term seemed it might be mistaken? I didn't include it in this formula.

    IF([due due]@row > today (), "Complete" (If correct, it should be written as IF([due due]@row> TODAY(), "Complete" )

    Please note that column name references are both spelling and case-sensitive.

    Please advise on the second term

    cheers,

    Kelly

  • That did it, thank you and yes that was indeed a mistake. Have a great evening and thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!