conditional formatting an expiry date by month and year

[misstoffeepenny]

hi all, I am trying to conditionally format a multi-date spreadsheet based on whether the month and year is passed this month and year.

eg. if a certificate is passed it's expiry date then highlight red, if its due to be renewed in the next month highlight yellow

I only have the cells formatted in mm/yy eg. feb-21
I have gotten the cells to format for the upcoming month but cannot get the expired dates to format.

can some help please?

note also some cells show n/a instead of a date as they do not apply to a particular site, I want these to have no formatting

 

[jasonb75]

Your screen capture is too small to read so I've based this on normal theory.

Where A1 contains the expiry date to be formatted, this formula as a rule should be able to highlight that cell when the current date has passes the end of the expiry month.

=IF(A1,EOMONTH(A1,0)<TODAY())

For posting samples, it is preferred that you use XL2BB, for which there is a link to a guide and download in my signature block below this post.

 

[misstoffeepenny]

thank you very much for your help, that was driving me mad for about 2 hours yesterday

 

[misstoffeepenny]

secondary question to this

if i now want the date to format green when a date in another column supercedes it how to i amend the formula or do i apply a seconday formula
eg. date is passed today format red

 

[misstoffeepenny]

sorry it posted it there

eg. date in cell H4 is passed today, format red, however if date in cell I4 is greater than H4 highlight green
so red date would be an action needing to be completed passed date due and green would be an action completed

 

[jasonb75]

It would need a second rule to be applied, although a rule can have multiple conditions it can only set one colour, so you need one for red and another for green.

For the green rule, try

=N(I4)>N(H4)

In the conditional format manager, check the order of the rules, If I remember correctly a newly added rule goes to the bottom by default, but if the new rule is above the old one in the list then the 'Stop if true' box needs to be checked.