Conditional formatting based on date doesn't clear when date is erased

[dragonabsurdum]

One of my conditional formatting rules is giving me some grief. This might be simpler with VBA, but I'm trying to avoid using a macro-enabled sheet since the file is shared overseas, I'm new to the company, and I'm not sure how they'd feel about me modifying the sheet that much.

Anyhoo...

The following rule will change the row to green when a date is entered into Column F (Date Responded), which is fine. The problem is that if someone clears the date, it remains green rather than being evaluated by the other rules. The entire column is formatted as "Date" and the other rules work prior to a date initially being entered into Column F.

=LEFT(CELL("format",INDIRECT("F"&ROW())))="D"

The closest I came to finding a fix was to have the rule evaluate whether the cell contained text in the format "dd/mm/yyyy", but there is the odd cell where 2 dates are entered. Is it possible to set conditional formatting to evaluate the cell based on the first 10 characters from the left? INDIRECT seems to be a bit finicky in conditional formatting and I haven't been able to get it to work. Every other rule seems to be working as expected.

The complete set of rules I have currently is as follows:
=ISBLANK(INDIRECT("A"&ROW())) <- no format, Stop If True (Column A is "Date Received")
=INDIRECT("F"&ROW())="P" <- pink fill, Stop If True (indicates response has been prepared for review, but has not been sent)
=INDIRECT("F"&ROW())="M" <- blue fill, Stop If True (indicates we are waiting for input from the client before responding)
=INDIRECT("F"&ROW())="N/A" <- green fill, Stop If True (no response required)
=LEFT(CELL("format",INDIRECT("F"&ROW())))="D" <- green fill, Stop If True (response sent)
=LEN(INDIRECT("F"&ROW()))>0 <- pale pink fill, Stop If True (invalid response error indication)
=INDIRECT("E"&ROW())<(TODAY()) <- red fill, Stop If True (response overdue, Column E is the due date)
=INDIRECT("E"&ROW())<(TODAY()+3) <- yellow fill (response due within 3 days)

 

[dragonabsurdum]

I believe I found a solution. I changed the problematic rule to:

=NOT(ISERROR(DATEVALUE(TEXT(LEFT(INDIRECT("F"&ROW()),10),"dd/mm/yyyy"))))

This appears to have fixed the issue with the row not reverting to no fill/red/yellow/etc. as relevant when the date is cleared from "Date Responded". It also gets around the problems I had if people decided to enter multiple dates into the same cell.