Expiration Date Formulas or Conditional Formatting

**alexandrao32** · 03-12-2013, 09:58 PM

Hi. I am working on a spreadsheet for work that consists of expiration dates for physician credentialing. I need a formula to use that will automatically change the text to red for all expired dates and yellow for those about to expire within 30 days. Every formula that I find consists of using (TODAY) OR (NOW), and because these use todays current date, this wont work as all of these expiration dates range for different reasons. For example, some formulas will end in -30 or +30, depending on the column. Can someone walk me through how to do this, please?

**dredwolf** · 03-12-2013, 11:08 PM

If you upload a sample (de-sensitized) showing what you have, and some expected results, we can maybe offer you a better, or at least personalized solution
The reason all those formulas ues the TODAY() or NOW() functions are that is the way to find out how far away from now the other dates are :

....I need a formula to use that will automatically change the text to red for all expired dates and yellow for those about to expire within 30 days.

For example:
(using cf style formulas to return TRUE/FALSE)
=TODAY()>'reference date'
will return an expired date based on yesterday be the last day
=TODAY()+20>='reference date'
will return true for dates coming due within 20 days

If your requirements for the expiry date calculation is different, we would need to see that to offer a more relevant solution

Hope this helps

**alexandrao32** · 03-13-2013, 09:47 AM

Ah, that makes sense. For example, my first line shows that Dr. John Smith has an appointment letter that expires on 12/31/2014, then the next line shows the next doctor with this expiration date and so on. Each date is different because each physician received theirs at different times throughout the years. I need a formula that will use the date listed in the cell, and change it to red once that date has expired, or will expire within the next 30 days. Would I then use the conditional formatting with these formulas?

=E7<Now()-30
=E7<Now()+30

**Kevin UK** · 03-13-2013, 11:02 AM

Hi alexandrao32

Select column E & then Conditional Formatting on the home tab.
Rule 1 - Expired:
=AND(E1<>"",E1<=TODAY())

Rule 2 - 30 days or less to expiry:
=AND(E1<>"",E1>TODAY(),E1-TODAY()<=30)

Rule 3 - 60 days or less to expiry:
=AND(E1<>"",E1-TODAY()>=30,E1-TODAY()<=60)

Or adjust the ranges if you only want to apply to a range of cells.