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

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 ✓

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The first thing to do is get the expected % Complete.

    =MAX(MIN(1, (TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row)), 0)


    The MIN and MAX functions will keep the expected % complete between 0 and 100.


    Then we can leverage this in a nested IF like so:

    =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"))))

  • @Paul Newcome Holy CRAP!! I am doing the happy dance over here.. One small thing.. I am getting a "DIVIDE BY ZERO" error for my milestone tasks (tasks where the Duration is "0" and has one or more predecessors). How do I fix that?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How exactly would you want it to work for those?

  • Good question! I am so used to having milestone tasks in MS Project, that I hadn't considered how they would work with some of my Smartsheet automations. 😂

    The milestones tasks will always have a duration of "0" days. These milestone tasks are special tasks I use for project tracking, building my critical paths, and reporting so they will never have a % completed. Therefore milestone tasks do not have a status.

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

    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")))), "")

  • PERFECT! That did the trick.. I can't thank you enough..

    Here's my final formula with the required mods:

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!