Conditional Formatting - due in 3 years

[shauneyd]

Hello everyone,

I am tearing my hair out with this one and hoping you can help.

I am setting up a spreadsheet where I have a list of dates for various reviews that take place.

The reviews are due every 3 years from the date it was last completed so I am looking to highlight the dates using conditional formatting like so:

1). Highlight cell amber when the date is 30 days from next scheduled review date
2). Highlight cell green if date is over 30 days from next scheduled review date
3). Highlight cell red if date has expired

I was hoping to achieve this using the date that the last review took place but having no luck so far.

Is this achievable without introducing a second column of dates? I have been having no luck with my formula so far.

Many thanks in advance for any help you can offer.

[shauneyd]

Sorry folks but I think I might have got it:

Red: less than or equal to NOW()+1095
Amber: less than or equal to NOW()+1065
Green: More than or equal to NOW()+1065

I am testing this at the moment but I think this might be it?

[shauneyd]

Sorry folks,

Ignore my last comment - that one didn't work but still trying.

[shauneyd]

Sorry folks but think I might have it now. I needed to convert the + to a - my formula as follows:

Red: less than or equal to NOW()-1095
Amber: less than or equal to NOW()-1065
Green: More than or equal to NOW()-1065

Hopefully this is it. Apologies for all the to-ing and fro-ing with the comments.