Conditional formatting for expiry dates

Question

I am trying to format dates expiring within 90 days and 30 days but no matter what formula I use, something is going wrong.

I have tried the below but they seem to go against each other so what I want red (expiring within 30days) comes up amber still.

"=AND(NOT(ISBLANK(B1)),EDATE(B1,12)-TODAY()<=30)" for the Red format

"=AND(NOT(ISBLANK(B1)),EDATE(B1,12)-TODAY()<=90)" for the Amber format

I've tried "=TODAY()-$B1<=30" (/90) but this doesn't work.

I have also tried "=NOW()+30" (/90) and this didn't work either but the same as above happens - I put the amber formula in and it all turns amber.

I'm following to the T and double checking everything but clearly I'm missing something or not putting the right question in to google!

*Sorry by the way, I don't know what tags to use.

Answer 1

Hi DEE,

I'm not sure if there's a standard Excel tag, but the Office Development tag is for developing Office Add-ins. I think for questions like this, you'd get a quicker answer from places like r/excel or Stack Overflow.

As for your specific question, I think the problem is that your rules need to be in the opposite order. I did a quick repro in the screenshots below that shows in one order, the yellow formatting always applies and in the other the red formatting is correctly applied. There's also a "stop if true" option you should probably check for your closer due date condition.

Hope this helps!