Conditional Formatting



I'm trying to get dates that are getting ready to expire in 10 days to turn
orange, then once the date has expired the date turns red. I've got the 2nd
criteria for the red text but can't get the formula to turn the date orange
10 days before expiry.....PLEASE HELP!!

Tom Hutchins

Assuming the dates are in column A, for the 10 days or less condition try
Formula Is

Select A1, then select column A, then select Format >> Conditional
Formatting and set up your conditions & formatting.

Hope this helps,



Hi Tom

Unfortunately this didn't work. Do you have any other suggestions?

FYI...I have to have 2 sets of criteria for the same column. One to turn
the date orange 10 days before expiry, and Two to have the date turn red once
the expiry is met.


Tom Hutchins

I didn't include the 2nd condition because your orignal post said you already
had that. Here is what I used for the 2nd condition (to turn expired dates

Formula Is =(AND(A1-TODAY()<=0,LEN(A1)>0))

I'm sorry the formula I supplied didn't work for you. It (and the 2nd
condition above) work in my test workbook whether the dates are entered as
real Excel dates or as text. If you are sure you are applying the conditional
formatting correctly, then can you tell me anything else about your workbook
to help figure out why the formatting is not working correctly? Or, maybe an
MVP will have an insight for us.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question