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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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