Conditional formatting formulas

Hi,

Assuming date is in A2 and expiry date is in B2 then enter the following in A2 and apply them to A2:A?? whatever the last row is.

Red CF

Formula:

=$B2>DATE(YEAR($A2)+3,MONTH($A2),DAY($A2))

Green CF

Formula:

=AND($B2>DATE(YEAR($A2)+1,MONTH($A2),DAY($A2)),$B2<DATE(YEAR($A2)+3,MONTH($A2),DAY($A2)))

Yellow CF

Formula:

=$B2<DATE(YEAR($A2)+1,MONTH($A2),DAY($A2))

Orange CF

=$B2-$A2<60

You are comparing two dates. What are they and where are they on the worksheet?

You were telling us there is an expiry date in your original post so I'm totally confused now that you suggest you don't now need an expiry date.

Yes but what are you comparing?

OK you have the date they received the certificate, what are you comparing that with in order to determine what the time difference is?

We're dealing with a conditional format here which implies you're making a comparison and painting the cell a different colour based on the result of the comparison.

Maybe you'd better upload your workbook so that we stop guessing what it looks like and what you have in mind. Add some typical figures and note what colour you expect specific cells to be.

I'm comparing the date in the cell to the appropriate category. In the example I used the expiry was 3 years from that date.

I've attached the workbook... feel free to look it over. I apologize if I'm not clear

I have been working on the First Aid column that expires 3 years from the date in the cell...

OK So you're comparing with today's date. It might have been helpful if you had mentioned that in the original post.

Use the original formulae I gave you starting in B2, but instead of where I mention A2, substitute B2 instead, and where I use stuff like Year(A2) substitute Year(Today())