Conditional formatting formula returning arbitrary results

[jamieanne84]

I am using the following formula to highlight a cell when the date within that cell is in the past and a document has not been received (a specific cell is blank):

=AND(I2="",E2<TODAY())

Column E contains the expected receipt date; column I contains a date if it has been received and left blank if it has not. I have tried several iterations of this formula with no luck, and this specific formula is highlighting random cells - for example, a few are highlighting when the date is in the past and column I contains a value, but some cells are highlighting when the date is in the future and column I is blank. I have rows right below each other with the same combination of values where 1 is highlighted and the other is not.

Some troubleshooting I have already tried:
1. Was a formula (referencing another date and adding 3 days) so I copy pasted special, values only; same result
2. Confirmed date formats match for both columns
3. Have revised the labels to be $I$2 (highlights every value), $I2 (same result as no $), I:I, $I:$I with no luck

[Tony Valko]

What cell(s) are you trying to format?

[jamieanne84]

All of column E which contains an 'expected receipt date' for all rows - it's an ongoing report with new rows added daily. If it's easier to build a formula for Column I, i'm flexible to that, but I had difficulty with that as well (that's the column that would either contain a date indicating receipt or remain blank).

[jamieanne84]

All of column E which contains an 'expected receipt date' for all rows - it's an ongoing report with new rows added daily. If it's easier to build a formula for Column I, i'm flexible to that, but I had difficulty with that as well (that's the column that would either contain a date indicating receipt or remain blank).

[Tony Valko]

Try this...

Clear all the rules that do not work.

Let's assume the range to format is E2:E10.

Select the ENTIRE range E2:E10 starting from cell E2.
Cell E2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=AND(I2="",COUNT(E2),E2<TODAY())

Click the Format button
Select the desired style(s)
OK out

[jamieanne84]

Unfortunately that did not work either, no cells are highlighted once that is applied. I'm starting to think I'm working in a corrupt workbook based on the outcomes of these formulas.

[Tony Valko]

Can you post the file?

We only need to see enough info that is relevant to the task at hand.

[jamieanne84]

I can't. Even with most of the data removed, it's still over 19 MB. I'm not sure how they created this file but that would explain why it's so slow and i'm having issues with it. Attached is a screen shot though if that helps.

[Tony Valko]

Are the dates true Excel dates?

In Excel dates are really just numbers formatted to look like dates. So, to test if an entry is a true Excel date you can use a formula like this:

=ISNUMBER(E2)

TRUE means it's a true Excel date.

FALSE would mean it's a TEXT entry that just looks like a date.

[jamieanne84]

Both columns E and I are returning as TRUE when i input that formula.

[Tony Valko]

Well, I can't think of any other suggestions to try without seeing the file.

Are you able to duplicate the problem if you make up a smaller sample file with similar data?