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

CrystalJ_Medtronic

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"

Paul Newcome

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

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

CrystalJ_Medtronic

@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

How exactly would you want it to work for those?

CrystalJ_Medtronic

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

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

CrystalJ_Medtronic

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

Happy to help. 👍️