IF Function based on a date of expiry

[bushfirey]

Hi All. I am doing a sheet based on medications and need a cell date to change colour and say certain things based on the expiry date (Expiry date in cell next to the cell I want to change based on the expiry date).

First rule - If date is expired as of today cell must say "Expired" and turn RED
Second rule - If date is expiring within 2 months cell must say "Expiring Within 2 Months" and turn Orange
Third rule - If date is expiring within 6 months cell must say " Expiring Within 6 Months" and turn Yellow
Fourth rule - If date is expiring 6 months or more cell must say "Expiry Greater than 6 Months" and turn Green

I have tried with conditional formatting and failed dismally. Any help would be appreciated.

Thanks
Dave

[bushfirey]

Update: I am using the latest version of Excel

[Saba Sabaratnam]

Hi,

I think that you need to do it two steps.

One is to work out the status of Expiry using a formula and then to use the result of the formula to conditionally format it.

The following formula will work the status of Expiry assuming the expiry date in A1 cell:

IF(A1=TODAY(),"Expired",IF(DATEDIF(A1,TODAY(),"d")<60,"Expiring Within 2 Months",IF(DATEDIF(A1,TODAY(),"d")<180,"Expiring Within 6 Months","Expiry Greater than 6 Months")))

As it seems that you are familiar with conditional formatting, I leave it to you. Also, you can include the above formula in conditional formatting (Use a formula to determine which cells to format option)

Kind regards

Saba