Excel conditional formatting formula not working as expected

Occasional Visitor

I have a column in Excel that contains dates. The column is G, and the data starts on row 2. I have a conditional formatting rule which is supposed to change the colour of each cell in that column, if the cell contains a date that is older than 30 days from today. However it formats every cell, except cell G2 which is left unformatted. The formula is =G2[=TODAY()-30. I have googled this and tried variations but they have the same result. I am using O365 Excel and have tried the desktop version and excel online. What should the formula be to achieve the result I would like? Going crazy here, having spent a couple of hours on something that I am sure I have done in the past with ease. Thank you for your support!

4 Replies


Select the range starting in G2.

Delete the existing conditional formatting rule, if any.

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 'less than' from the second drop down.

Enter the formula =TODAY()-30 in the box next to it.

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

Hi Nikolino, thank you for responding so quickly, Unfortunately that is one of the methods I had already tried, and yielded the same incorrect formatting result. I do have a solution now though. Thanks again for your time.
This method worked! Thank you so much