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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Ok. In that case we can just use the standard IFERROR function to output a blank.

    =IFERROR(original_formula, "")


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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!