Conditional Formatting in Excel 2010 - Shading Rows

1
votes

I am trying to use conditional formatting in an excel 2010 spreadsheet. The spreadsheet has seven columns of data, but for the formatting, only the first three are relevant. The first two are dates and the third either has "yes" typed in it or is blank. I want a row of the spreadsheet to be highlighted if the following is true:

either today's date is more than two days after the date entered in column A, column B is empty, and column C is empty

or today's date is more than two days after the date entered in column B and column C is empty.

The formula I attempted to use is

OR(AND((TODAY()-$A2)>2,ISBLANK($B2),ISBLANK($C2)),AND((TODAY()-$B2)>2,ISBLANK($C2)))

but nothing is highlited in the spreadsheet. I'm not very familiar with conditional formatting beyond a few basic examples, so I tried to just modify formulas I found on the internet. Because of that, I don't really understand what is going wrong with my attempted formula. Thank you in advance for any suggestions.

1
excelformattingexcel-2010
So....looks like we've found a bug in Excel, try the new edited answer, it works. – Daniel Möller
This is still not working for me. I tried a similar thing in Excel 2011 for Mac, and I'm having the same problem. I want cells to be highlighted when the date in a cell is more than two days ago. When I type in TODAY()-that cell's date, I am returned a date and a formula of IF(TODAY()-that cells's date > 2, 1, 0) shows that the condition for formatting has been met, but there is still no shading in my sheet. Sorry if this was a little convoluted. There can't be a bug in every version of Excel, right? – user2355833

1 Answers

0
votes

I tried, and it doesn't work indeed, even if the formulas are ok.

But if you use $A2 < $X2 and $B2 < $X2 it works. Make X2 (or any other cell you want) to be =Today() - 2

It's a workaround to what looks like a bug of Excel.

I didn't try the Isblank, but you can substitute them for $C2 = "", for example, if you need.