Using Conditional Formatting After A Certain Number of Days Have Elapsed

Question

Apologies in advance, I've looked at dozens of these responses but mine is just not working. I have tried 'Edate' 'IF' 'Today' etc. I have a cell in which a date is entered when a report will be submitted. If 60 days have passed after that date, I want to change the cell colour. Eg. report due 22nd Nov 18 - 60 days after that will be 21st Jan 18. Essentially I understand it would be =TODAY()=>60 No matter what number I enter the cell turns red automatically which shows it's not correct.

I would be most grateful if anyone can help thanks.

Answer 1

Select the cell(s). In the following, I will assume that B2 is the active cell within the selection.

On the Home tab, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND(B2<>"",B2<=TODAY()-60)

Remember, B2 is the active cell.

Click Format...

Activate the Fill tab,

Select red.

Click OK, then click OK again.

Answer 2

HansV, many thanks for your reply, that's different to anything I have found so far. I will try it later when I am free. Thanks so much.