Formula isn't working properly

dmsbhs ✭✭
edited 06/15/23 in Formulas and Functions

I created the below formula to calculate project schedule health and cannot seem to have it return expected outcomes. Can someone help me get this right?


  • If a task is not completed and the target date is either today or in the past; OR
  • if the end date (end date is captured when %Compete is 100%) is 5 days after the target end date (I'd like this to be 5 net work days if possible)

Yellow (this one is a bit more involved)

  • if % Complete is less than than (count of days since start date)/(duration) difference of start date and today (the formula has a *-1 and a -1 to not count the first day and keep responses positive); OR
  • if end date is within 5 days of target end date


  • default to green if blank
  • if the above conditions aren't found should be green
  • if completed on or before target end date

=IF(OR(AND(Status@row <> "Complete", [Target End Date]@row <= TODAY()), [End Date]@row > [Target End Date]@row + 5), "Red", IF(OR([% Complete]@row < ((((NETWORKDAYS(TODAY(), [Start Date]@row)) * -1) - 1) / Duration@row), [End Date]@row > [Target End Date]@row + 5), "Yellow", "Green"))

Edited to bold logic that isn't working properly and added third bullet to Green


  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/15/23

    If you want it to be yellow if it is within 5 days after the target date you need to change it to < than instead of >

    [End Date]@row < [Target End Date]@row + 5),"Yellow"

    Will also need to add criteria for [End Date]@row>[Target End Date]@row so it won't mark it as yellow until the Target End Date has passed.

    If you are wanting it to be yellow within 5 days before the Target end Date then you would just need to change the + 5 to a -5

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!