Conditional Format Formula Based on % Complete & Time Remaining from Today to Month End

[kschmit1]

Good morning,

I am attempting to apply a conditional formatting formula that will change the cell color based on the percentage complete and the number of days remaining. For example assuming 100% is due complete by 6/30/2022, if there are more than 20 days remaining until 6/30/2022 and the item is greater than or equal to 30% complete that would be green, if less than 20 days and less than 60% complete = yellow, etc. there is a general key listed in my example. I attempted some AND formulas using TODAY() but was unable to find success. I successfully applied the formula =B5>=$I$1 to get the cell green, but the adding =TODAY()>$B$4-20 in an AND formula did not result in the expected result.

[ELeGault]

=AND($B5>=$I$1,TODAY()>=($B$4-20))

Your formula of today > B4-20... B4 - 20 days is 6/10... so your formula is FALSE as a result.
Formatting only applies when the result is TRUE

[dosydos]

I made some formulas for June conditional formatting as shown below and attached.

[ELeGault]

See where Dosydos updated your formula to get the difference of today and B4...
=AND(B5>=30%,$B$4-TODAY()>=20)

Instead of the way you had it written
=AND($B5>=$I$1,TODAY()>=($B$4-20))

HOWEVER, it is not wrong the way you have it written for what you intend... if the end of the month is the end of the cycle and you want to say that today is within the last 20 days of the month... you have done that! but today is just not within that window so you will not see that happen.

Meanwhile neither of these formulas restrict the view to within the scope...

If we are saying that we want to highlight when it is 0 - 20 days, there is nothing that prevents it from entering a negative thresh, which means everything in the June box will highlight once it is within that 20 days as well as -1000 days later!

Just things to consider, no saying how far this scope is meant to live so may be moot

[dosydos]

If we are saying that we want to highlight when it is 0 - 20 days, there is nothing that prevents it from entering a negative thresh, which means everything in the June box will highlight once it is within that 20 days as well as -1000 days later!

Technically if they show 91% or higher completion then they wont highlight at all even if -1000 days because of the AND() function. I agree though there certainly seems to be gaps between coverage analysis.