Conditional Formatting for Dates

[Kirstyamanda]

Hi there, me again!

Okay so I am trying to add a column that has the dates of the last inspection of a ship done by its Manager; they can't exceed 12months between visits; so I would like to be able to highlight the ships that have dates that are a year or more past today's date (and future dates upon which anyone is looking at the spreadsheet) in red, and have those that have been done within the last year of the date to be highlighted as green. I've looked at the template format formulas in the conditional formatting already, but don't think any of those will be of any use to me.

Could anyone give me a hand please?
Many thanks in advance!

[Pete_UK]

It would help if you attached a sample Excel workbook, as you haven't given any details of the columns where the dates are located, so any proposed solution would have to guess these.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete

[Kirstyamanda]

I've had to remove all sensitive info - the formula will apply to column R in relation to cell C11. The formula will only apply to the Overview Sheet.

[Pete_UK]

You seem to have a number of Conditional Formats already applied, though they all relate to "Cell Value Is ...". It is much more flexible and powerful if you make use of "Use a Formula to determine which cells to format" when you set up a new rule, and with this option chosen a dialogue box pops up which allows you to enter a formula to govern the criteria. Your formula would probably be something like:

=$C$11-$R15 > 365

although as you have letters in column R then it might be better to use:

=AND(ISNUMBER($R15),$C$11-$R15 > 365)

Having said all that, though, I'm not really sure what you want, as you already have the sheet filled with pink, green and yellow cells, so could you explain exactly what you want to achieve?

Hope this helps.

Pete

[Kirstyamanda]

The cells in column R are all red and they shouldn't be - only the ones that are past a year of todays date. The one's that are within a year should be green. That's exactly what I want to achieve, I don't know how to explain it any better, apologies.

[JohnTopley]

There are 5 conditions on Column R two of which are > C11 and < C11 so unless they equal C11 they will be RED.

And GREEN test is for "Y" not a date check.

I left the "N" and "Y" conditions, delete the other RED and used Pete's formula

=AND(ISNUMBER($R15),$C$11-$R15 > 365) RED

=AND(ISNUMBER($R15),$C$11-$R15 < 365) GREEN

[Kirstyamanda]

Awesome, thank you very much!

[Pete_UK]

Strictly speaking, the second one should be <= 365, otherwise it won't be coloured if it is exactly a year.

Pete