Conditional formatting to highlight if its over a year

Question

Conditional formatting to highlight if its over a year

Anonymous

Good morning,

I have a column of dates formatted as DD/MM/YYYY

The column is H and starts on Row 3 and goes down to row 84 (Although I'd prefer if the conditional formatting could go on and on in the same column.

I basically want the cells to highlight green if the date is within a year of todays date, orange if over a year, and red if over 2 years. Id also like the cell to go blue if I write 'Never'.

Thank you in advance for your help - I've got a headache!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Answer 1

Select H3:H1000 or however far down you want.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'not equal to' from the second drop down.
In the box next to it, enter the formula

=""

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

Repeat these steps, but with "greater than or equal to", with the formula

=EDATE(TODAY(), -24)

and orange as fill color.

Repeat them again, with "greater than or equal to", with the formula

=EDATE(TODAY(), -12)

and green as fill color.

Finally, repeat them with "equal to", with the formula

="Never"

and blue as fill color.

Share via

Conditional formatting to highlight if its over a year

1 answer