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

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"

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"

Find more posts tagged with

Accepted answers

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

All comments

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

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.