Manisha has attended:
Excel Intermediate course
Conditional Formatting
Hi,
Could you please tell me how I can conditional format the below scenario.
I have two date columns (Column D & E), and a percentage completed column (Q)
If there is 30 days remaining on either side of the date in column E, and the percentage completed is over 80% I would like that column to be green.
and then less than a month but between 21% to 79% would be amber, and less than a month and below 20% would be red
RE: Conditional Formatting
Hi Manisha
Thanks for your enquiry.
You need to use the Formula option with Conditional formatting. Any formula you the answer must result in a TRUE or FALSE result. You will create 3 separate test.
Using the AND function will allow you to have multiple criteria and the final answer will be either TRUE or FALSE. Because you have multiple tests you will need to select the option to STOP WHEN TRUE on the conditional format so just one rule is applied.
Rule 1
GREEN =AND((Col E - Col D) >= 30 , % Complete >= 80%)
Rule 2
AMBER =AND((Col E - Col D) <= 30 , % Complete >= 21%, % Complete < 80%)
Rule 3
RED =AND((Col E - Col D) <= 30 , % Complete <= 21%)
I hope this helps. Please get in touch if you need any further assistance.
Kind regards
Dennis
