Struggling with if formula to show overdue task and activate red flags

I have been playing around with the IF formula and thought I finally got the hang of it. However my "overdue" statement is not working and just set's it to "In Progress" when percent complete is not 100% on Todays date. I also want to do an At Risk statement but need to figure out what is wrong with the current formula.

The example I used is

=IF([Percent Complete]@row = 1, "Complete", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", ""))))

This is my current formula, I tried it with just TODAY() but that did not work either just sets it to in Progress.

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF([Percent Complete]@row > 0.01, "In Progress", IF(AND([Percent Complete]@row < 1, [End Date]@row <= TODAY(1)), "Overdue"))))

and

=IF([Percent Complete]@row = 1, "Green", IF([Percent Complete]@row = 0, "Blue", IF([Percent Complete]@row > 0.01, "Yellow")))

If you can assist, I feel like I am going in circles here

Find more posts tagged with

Formulas

Accepted answers

All comments

Mark.poole

@vanstadenster

The way If statements work is that it will look for the first True statement. and then continue from there. I would move where you have the overdue statement. What's happening is that it is finding the In Progress statement as true so it stops searching for the false "The over due" Try this instead.

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [End Date]@row <= TODAY()), "Overdue", IF([Percent Complete]@row > 0.01, "In Progress"))))

vanstadenster

Awesome going to try that - thank you so much!!!

Will let you know if this solves it.

Much appreciated.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.