Compare date in cell against dates in range of cells

[Closed]
Report
Posts
1
Registration date
Thursday January 31, 2013
Status
Member
Last seen
January 31, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
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

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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