Annual Expiration Conditional Format

I work in IT tracking various users certificates.

I have to sift through hundreds of users on excel looking at their expiration date, and manually selecting them from the filter list, and then copy them to a sheet so departments know to update the certificate.
I want a conditional format rule that simply says hey this user's certificate has expired exactly a year ago from today, make it red, and spit it out a copy of those users on another page.

Thanks in advance for the help!!!

Hi,

I'm assuming you want to know whether the expiry date is greater than a year ago rather than EXACTLY a year ago.

With the expiry date in A1 add a conditiona format to A1

Formula:

EOMONTH(A1,11)+DAY(A1)<TODAY()

In order to extract the >1 year values you'll need a helper column, say B1 in which you have the formula

Formula:

=IF(EOMONTH(A1,11)+DAY(A1)<TODAY(),"Y","")

Then you could use an Advanced Data Filter to extract all the column B values using "Y" as the criteria. ALternatively you may find it it sufficient to use a simple autofilter on column B and select "Y".

I couldn't quite figure out how to make it work on another sheet, but I got the dates to change around from another post you had.

Please look at my attachment. I copied and pasted the dates from another sheet (with no formatting) and placed them into mine but there are quite a few cells where the colors and font don't keep their formatting rules.

Date Seperator.xlsx

Also two other factors
1. How do I make the name turn red or green if column B or C is red.
2. If I add a third column D with yes or no answers how do I make it red if its no.

Hi,

There are quite a lot of redundant cond. formats and I'd like to start from scratch. Please explain the rules for the red,green or amber(?)

Can you clarify the new column D. Is this to be a formula or are you manually adding the yes/no?
In any case just set the conditional format in D3 using the 'Format cells that contain' and enter "No", set the format to red and apply it to the whole of column D.

Hi,

I'm puzzled by all your conditional formats, circa 35 in the case of each cell in column D. Futhermore I don't understand why one or two of them use a MOD(Row(),2) formula since these are nothing to do with dates and will in the absence of other higher priority CFS create a 'pyjama' stripe effect on successive cells.

As I say I'd like to start with a clean sheet with no CFs and add new ones as appropriate. Hence I need to understand the precise rules for painting any cell red or green. What you currently have seems overcomplicated.

When it comes to the CF for the single name cell A3 and if I understand the requirement, that's going to need either a helper column or two or some fancy array formula to count the number of different coloured cells.

Hi,

I arranged it so that if A is blank then neither B nor C are coloured.

If A has a value then the colour stays white until there is stuff in B:E to evaluate.

If as you say B for example has a date and nothing else in C:E what colour should A be? i,e, what is the specific rule?