Conditional Formatting for Impending Dates Several Months Out

I'm looking to apply conditional formatting to a set of dates.

Basically, we have several projects that have end dates several months out. I'd like the date to highlight one color when it's less than 18 months until the end date from the current date and a different color if it is less than 12 months.

Is there a way to do this? I'd also like to do this without using days if possible (e.g. 18 months = 540 days).

Originally Posted by Aardigspook

Use EDATE in your CF formula.

Select your range of dates (let's say B2:B20).
Go to Conditional Formatting → New Rule → Use a formula to determine which cells to format.
Enter this formula:

Formula:

Please Login or Register  to view this content.

Use the Format button to choose the highlight you want (font colour, fill colour, etc).
With the range still selected, repeat with this formula:

Formula:

Please Login or Register  to view this content.

Choose your other highlight.

Make sure you do them in that order. If you go to Conditional Formatting → Manage Rules, you should see the <12 months rule at the top.

The cell reference in the formula should be the top-left cell of your range - Excel will automatically change it for the rest of the range you have selected.
If you need to expand the range later, do it using Conditional Formatting → Manage Rules. You'll see the Applies To range for each rule there.

Hope that helps.

Worked perfectly! Thank you!