Multiple IFS Formula for Status based on % Complete + Finish Date


I broke a working formula by changing the conditions and hoping to get some help to get this to work.

Objective: Create a formula to reflect the status of task based on [% Complete] and [Finish]. These are the values I am trying to generate in one formula:

  • Red (Late) = If % complete is less than 100% and Finish Date is in the past
  • Yellow (At Risk) = % complete is less than 85% and Finish Date is within the next 3 days. (% complete greater than 85% + Finish Date within next 3 days is Green "In Progress")
  • Green "In Progress" = % complete is greater than 0% and Finish Date is greater than today + 4 days
  • Blue "Not Started" = % complete is 0% and Finish Date is greater than today + 4 days
  • Gray "Complete" = % complete is 100%
  • Purple "On Hold" = % complete is “H”
  • Black "Cancelled" = % complete is “X”

This is the formula I have so far (it doesn't work) and it is not complete. Not sure why the formula is not looking at the % complete for "At Risk" in the image below:

Thank you, in advance, for your help with this formula.



  • markkrebs
    markkrebs ✭✭✭✭✭✭

    You are looking at a % so instead of 85 put a .85

  • Lisa Matthews
    Lisa Matthews ✭✭✭✭✭

    Hello @markkrebs , I did try .85 and the result was a blank cell for rows which should have been "At Risk" or "Complete". Not sure how to fix this formula, still. Thank you for trying to help.


  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Lisa Matthews

    Your first problem is your formula starts with =IF([% Complete]@row=85, but then it doesn't tell SmartSheet what to do if that equals 85. You just go right into the next Nested IF without resolving the first IF statement's True statement.

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Yeah this.... so the rest of the formula runs only IF % complete=85... so you almost always get a blank cell.... its best to start with the most definitive statements in a nestled IF formula.

    Another tip - nestled IF statements work from the first applicable statement, so you want to start from the most definitive statement... in your case I would order your IF statement Gray, Purple, Black, Red, Blue, Green, Yellow.

    Finally, I'd consider separating 'Status' i.e. on hold, cancelled etc. from % complete. so your % complete figure isn't "H" or "X"... this will cause issues building metrics and generally referencing the % complete column.

    I'm too lazy to figure out the details. cheers.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!