Compare date in cell against dates in range of cells

Closed
Grimsby21
Posts
1
Registration date
Thursday January 31, 2013
Status
Member
Last seen
January 31, 2013
- Jan 31, 2013 at 03:04 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Jan 31, 2013 at 11:02 AM
Hello,

I am trying to use conditional formatting to highlight a cell if the date in that cell is the same or earlier than dates in the rest of the row. The problem is that some of the cells do not have dates in so are blank or they contain text.

A1 is the expected completion date. The rest of the columns are populated with action dates or text and I would like to quickly see if an action date has been given which is on or after the completion date. I can get this to work on rows that do not contain text but not where cells contain text.

Is this possible? Thanks in advance for your help. I am using Excel 2003.

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Jan 31, 2013 at 11:02 AM
Hi Grimsby,

I used column A for completion date and columns B, C and D for action dates.

Conditional format, formula is:
=OR(AND(N(B2)>0,B2>=A2),AND(N(C2)>0,C2>=A2),AND(N(D2)>0,D2>=A2))

Text will now be recognised as the value 0 and thus be negated.

Best regards,
Trowa
0