formula running the status column

This is the formula in status. The issue is that if the date is TODAY either in START or in END Date column the status turns to NOT STARTED.

I need the status NOT STARTED only to show up when the start date is in the future =IF(Level@row <> 0, IF(Level@row <> 1, IF([End Date]@row = "", "", IF([Canceled?]@row = true, "Canceled", IF([On Hold?]@row = true, "On Hold", IF(AND([Start Date]@row < TODAY(), [Task Complete?]@row = false, [End Date]@row > TODAY()), "In Progress", IF(AND([Start Date]@row < TODAY(), [Task Complete?]@row = false, [End Date]@row < TODAY()), "Overdue", IF([Task Complete?]@row = true, "Complete", "Not Started"))))))))

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Agata Wozniak

    It looks like you're just missing an = sign before some of your date comparisons!

    For example, instead of:

    IF(AND([Start Date]@row < TODAY(),

    You'll want to put:

    IF(AND([Start Date]@row <= TODAY(),

    See Formula Operators in this Help Article: Create and Edit Formulas in Smartsheet


    With this in mind, try:

    =IF(Level@row <> 0, IF(Level@row <> 1, IF([End Date]@row = "", "", IF([Canceled?]@row = true, "Canceled", IF([On Hold?]@row = true, "On Hold", IF(AND([Start Date]@row <= TODAY(), [Task Complete?]@row = false, [End Date]@row >= TODAY()), "In Progress", IF(AND([Start Date]@row <= TODAY(), [Task Complete?]@row = false, [End Date]@row <= TODAY()), "Overdue", IF([Task Complete?]@row = true, "Complete", "Not Started"))))))))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!