Excel conditional formatting formula not working as expected

Copper Contributor

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!

10 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
I have a column to which I am trying to apply conditional formatting. The contents are from a =if formula [more specifically:(=IF(D2="H",IF(Q2<3,1,IF(AND(Q2<4,Q2>2),3,IF(AND(Q2<5,Q2>3),4,6))),(IF(D2="S",(IF(Q2<2,3,IF(AND(Q2<4,Q2>1),6,8))),"none")))]. I can not get conditional formatting to work. I have ensured that contents are formatted as general. I have clicked "Text to Columns". I note that if I simply enter the value of the if statement in an adjacent column, that column I can conditionally format normally. Please assist. Thank you.


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 

Thank you Hans.  Please see:



Column R contains  the calculated field that I would like to conditionally format.  I have copied the values from column R to column S and applied conditional formatting to both R and S.  It works as intended in column S, but not in column R.


Note that the basic spreadsheet was downloaded from our payroll provider, so columns A-O contain some weird formatting.

PS I don't see a way to attach files to this forum directly, is that correct?


Most users cannot attach files in this forum; some can but I don't know the criteria.

Your formula switches from returning number values such as 6 or 8 in row 2 to returning text values such as "6" or "8" in row 3 and below; that's why the conditional formatting doesn't work.

You can simplify the formula in R2 to


then fill down.

Thank you Hans, that worked! But why is the format of the results of my formula different in different cells, even though I formatted them all as "General"?


This is what I see in your workbook: