On 7/03/2012 2:28 PM, MPatterson wrote:
Hi,

I am working on a spreadsheet at the moment for my boss where I need to
make the cell change color due to VISA's expiry dates

For example if a VISA is still valid the cell will be green
if the VISA is due to expire in 6 months the cell will be orange
if the VISA is due to expire in 3 months the cell will be red

I am working in Microsoft Excel 2010.

Help please!!!!




Hi

here's a formula that will give you the days you need to change the cell
display message in your cell of choice. it is based on True( Euro
Calendar ), set to False if you want ( US Calendar )

=IF(DAYS360(A2,B2,TRUE)<90,"EXPIRES IN 3 MONTHS OR
LESS",IF(DAYS360(A2,B2,TRUE)<18190,"EXPIRES IN 6 MONTHS OR LESS","MORE
THAN 6 MONTHS BEFORE EXPIRY"))

Then using CF set the condition for each:
EXPIRES IN 6 MONTHS OR LESS = Orange Fill
EXPIRES IN 3 MONTHS OR LESS = Red Fill

HTH
Mick.