Specific conditional formatting

New Contributor

Hi, I am having troubles to achieve the following conditional formatting:

- I would like a specific cell to be highlighted in specific color and filling exactly 5 days before today. So far I managed to achieve that but the cell remains formatted afterwards for any day less than 5. I need it to be formatted only today -5 and today -4,-3,-2 etc. to remain without formatting. Kindly can someone help me?

5 Replies

@emilkotsev Something like this?



Hi, thanks for the rapid response! I have tested it and indeed it's triggered 5 days before the today date 06 November 2022 however if I change the date to 07 November 2022 it still remains formatted. I want the formatting to be applied only on the date minus 5 days.


Select the cell or cells.

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

Select 'Format only cells that contain'.

Leave the first drop down set to Cell Value.

Select 'equal to' from the second drop down.

In the box next to it, enter the formula =TODAY()-5

Click Format...

Specify the desired formatting.

Click OK, then click OK again.

That worked! Thank you so much!!!!!!!

@emilkotsev Sorry, I don't follow.