Forum Discussion
Excel conditional formatting formula not working as expected
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!
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.
- 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?
- This method worked! Thank you so much
- =$G$2=(TODAY()-30)
- 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.
