Struggling with if formula to show overdue task and activate red flags


I have been playing around with the IF formula and thought I finally got the hang of it. However my "overdue" statement is not working and just set's it to "In Progress" when percent complete is not 100% on Todays date. I also want to do an At Risk statement but need to figure out what is wrong with the current formula.

The example I used is

=IF([Percent Complete]@row = 1, "Complete", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", ""))))

This is my current formula, I tried it with just TODAY() but that did not work either just sets it to in Progress.

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF([Percent Complete]@row > 0.01, "In Progress", IF(AND([Percent Complete]@row < 1, [End Date]@row <= TODAY(1)), "Overdue"))))


=IF([Percent Complete]@row = 1, "Green", IF([Percent Complete]@row = 0, "Blue", IF([Percent Complete]@row > 0.01, "Yellow")))

If you can assist, I feel like I am going in circles here



  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/06/24


    The way If statements work is that it will look for the first True statement. and then continue from there. I would move where you have the overdue statement. What's happening is that it is finding the In Progress statement as true so it stops searching for the false "The over due" Try this instead.

    =IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [End Date]@row <= TODAY()), "Overdue", IF([Percent Complete]@row > 0.01, "In Progress"))))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!