I am utilizing the following formula to return a value based on the values in my columns:

=IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row > TODAY()), "Scheduled", IF(AND([Work Needed]@row = true, [Scheduled Inspection Date]@row < TODAY()), "In Progress", IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row < TODAY(7)), "Completed", IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row < TODAY(7)), "Completed"))))

I would like to add "Not Scheduled" if the Work Needed Column is false and the Scheduled Inspection Date column is blank. I have been at it for hours with no solution. Any help is greatly appreciated in advance!


  • Kelly Drake
    Kelly Drake Overachievers Alumni

    So first... it looks like the last two IF statements in your formula are identical.... is that an error or are you missing one set of conditions for 'Completed'? Also "= true" is not the right syntax for smartsheet, you would need to use = 1. And it looks like you would never end up with a line getting flagged as 'Completed'...

    Also, I typically work from Completed backwards to not started to make sure things land in the spot on the process. But it looks like the only one that has Work Neded checked is for 'In Progress' so you could pull this one to the front and simply your formula a bit

    What about the following....

    = IF([Work Needed]@row = 1, "In Progress", IF(ISBLANK([Scheduled Inspection Date]@row), "Not Scheduled", IF([Scheduled Inspection Date]@row < TODAY(7), "Completed", IF([Scheduled Inspection Date]@row > TODAY(), "Scheduled"))))

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Michelle Fayed
    Michelle Fayed ✭✭✭
    edited 02/29/24

    @Kelly Drake Worked PERFECTLY! Thank you so very much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!