HELP! My status formula is not quite working as expected..

Options
CrystalJ_Medtronic
CrystalJ_Medtronic ✭✭✭✭
edited 09/07/23 in Formulas and Functions

I am struggling with a game of formula "whack-a-mole" lol

This is my status formula:

=IF([% Complete]@row = 1, "Complete", IF(OR(Finish@row < TODAY(), (Finish@row - TODAY()) / (Finish@row - Start@row) < [% Complete]@row), "Behind Schedule", IF(Start@row > TODAY(30), "Not Started", IF(Start@row > TODAY(), "Starts within one month", "In Progress"))))

I am not quite getting the results I want/expect.

My test task contains the following data:

  • Start: 9/1/2023
  • Finish: 9/18/2023
  • Today's Date: 9/7/2023

I would expect that if the "Finish" date has passed or if "% Complete" is less than 64% that the status will be "Behind Schedule". However the opposite is true. When the "% Complete" is less than 64%, the status shows "In Progress" and if the "% Complete" is greater than or equal to 64% the status will be "Behind Schedule".

If I change this statement:

IF(OR(Finish@row < TODAY(), (Finish@row - TODAY()) / (Finish@row - Start@row) < [% Complete]@row), "Behind Schedule"

to

IF(OR(Finish@row < TODAY(), (Finish@row - TODAY()) / (Finish@row - Start@row) >= [% Complete]@row), "Behind Schedule"

Tasks that should show a status of "Starts within one month" will instead show a status of "Behind Schedule"

Here's what I need:

  • If the % Complete is 100, return "Complete"
  • If Finish Date is less than Today or if the % completed is less than the Finish Date minus today's date divided by the Finish Date minus the Start date, return "Behind Schedule".
  • If the Start Date is in more than 30 days, return "Not Started"
  • If it doesn't match any of the criteria above, and if the Start Date is greater than today, return "Starts within 1 month". If none of the above criteria are met, return "In Progress"

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!