Flag based on % completion relative to end date

Jbro

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

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.