Conditional format according to dates

[karen malcolm]

Hi,

I have a spread sheet which contains the dates procedures were last reviewed. I would like them to change colour to green if they have been reviewed within last 182 days (6 month) amber if they were reviewed more than 182 but less than 365 days ago and red if they have not been reviewed for more than 730 days. Does anybody have any help, my conditional formatting experience is beginner.

 

[Joe4]

Welcome to the Board!

Dates in Excel are tracked as whole numbers, specifically the number of days since 1/0/1900. Knowing, this you can do what you want with simple subtractions.

For example, the Conditional Formatting formula for more than 182 days but less than 365 days would look something like this (for a date in cell A2):

=AND(TODAY()-A2 > 182, TODAY()-A2 < 365)

 

[TimNevins]

You'll need three conditional formatting entries. The one above and then the one for <182 and one for >365.

You can also use an Icon Set that would do all evaluations in one rule. Select the fields, click Conditional formatting, pick one. Then click on Conditional Formatting, Manage Rules, then select the entry, and click Edit Rule. You can then enter your two values and away you go.

Good luck,