Conditional Formatting for Dates

Question

Hello

I have a column showing a list of expiry dates, formatted as mmm-yy. I would like it to automatically format so that:

• Any dates that are in the past are filled with red
• Any dates that fall within three months from today are filled with orange
• Any dates that fall within four and twelve months from today are filled with yellow
• Any dates further into the future than twelve months remain unformatted.

I've tried to fathom out how to do this, but am failing miserably, so all advice gratefully received. Thank you.

Answer 1

Change all the $G2 to G15 as your first row of selected range is 15.

Answer 2

Select the dates.

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 'less than' from the second drop down.
In the box next to it, enter the formula

=EDATE(TODAY(), 12)

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

Repeat these steps, but with

=EDATE(TODAY(), 4)

and orange as fill color.

Finally, repeat them again with

=TODAY()

and red as fill color.

Answer 3

That's worked brilliantly, thank you!

What would I need to do to get the whole row to highlight in those colours, rather than just the cell with the date in it?

When I say the whole row, there is only data in Columns A to G, so it would only really need to be through those.

Thank you again!

Select the dates.

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 'less than' from the second drop down.
In the box next to it, enter the formula

=EDATE(TODAY(), 12)

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

Repeat these steps, but with

=EDATE(TODAY(), 4)

and orange as fill color.

Finally, repeat them again with

=TODAY()

and red as fill color.

Answer 4

Suppose your date in column C, Select A2:G12

Conditional formatting, Use a formula

=AND($C2 < EDATE(TODAY(),0),$C2<>"")

=AND($C2 < EDATE(TODAY(),4),$C2<>"")

=AND($C2 < EDATE(TODAY(),12),$C2<>"")

Answer 5

I'm getting some random results with that. My dates are in Column G.:

August 24 returning different formatting in different rows.

October 2028 showing as in the past.

June 2027 showing as within the next year.

Heeeeeeeeeeeeeelp...............

Thank you.