Excel conditional formatting formula not working as expected

[Simes888]

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!

[#2]

=$G$2=(TODAY()-30)

[#3]

@Simes888 

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.

[#4]

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.

[#5]

This method worked! Thank you so much

[#6]

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.

[#7]

@sleibo 

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?

[#8]

@Hans Vogelaar 

Thank you Hans.  Please see:

https://www.dropbox.com/scl/fo/ebou2bzznsluxesftn5c2/h?rlkey=mg2ehx55asxqccrgggizj7q5v&dl=0

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?

[#9]

@sleibo 

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

=IF(D2="H",IF(Q2<3,1,IF(Q2>4,6,Q2)),IF(D2="S",IF(Q2<2,3,IF(Q2>3,8,6)),"none"))

then fill down.

[#10]

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"?

[#11]

@sleibo 

This is what I see in your workbook: