Conditional formatting

[jak68]

Hi,

I am looking for some help with a spreadsheet that we are using to highlight certificate expiry dates for suppliers.

I am looking for a specific cell (use A1) to change colour and produce a specific text when a year after a date is reached in another cell (Use A2).

I want A1 to stay green and say 'ok' until a year after the date in A2 and then once it is year after the date in A2, A1 should turn to red and say 'Expired'.

can anyone help please?

[Fotis1991]

You can not use formula,in the same cell that have some values(in this case dates)

For coloring the cells, try these in Conditional Formatting rules.


=year(a1)=year(a2)+365 >>green

=year(a1)>year(a2)+365 >>red

[sagrok]

JAk

You can use If condition formula and conditional format,

my work will be helpful to solve your problem..

see my attached sheet

Rgds

SagroK