Flag based on % completion relative to end date

Hi guys,

I'm trying to create a formula that flags, Green, Yellow, Red, when a specific % is closer to the end date. For example, if I have 95% and end date is 10 days ahead, flag should be green, however, if I have 25% completion and I am in 10 days of reaching the end date, it should be red.

I was thinking of also, having a date variance, such as end date - today = X, then using this X with the % completed flag.

Any ideas on how to approach this?

Thanks,

Juan

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try this to get a variable % expected based on the dates to compare to the actual % (below has variance thresholds for each color set so that if the actual is greater than or equal to the expected then green, if the actual is within 10% of the expected then yellow, and if it is more than 10% behind then red).

=IF([% Complete]@row>= MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0), "Green", IF([% Complete]@row>= MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0) - 0.10, "Yellow", "Red"))

Jbro

Thanks, can you provide an example? Trying to picture the formula here. We are getting there!

Paul Newcome

That is the formula there at the bottom of my comment.

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.