Conditional formatting to change colors based on time lapsed from another cells date.

I have a tracker that tracks when reports are sent/received. Right now I put in the sent date, and the received field is formatted to turn certain colors depending if the report is late, turned in, turned in but late, or if sent but deadline not passed.

I need to add two columns in between. One for Appeals received, then Appeals responded too. Same rules. I attached a spreadsheet so hopefully that helps, but I need D & G to be tied together, and then E & F.

Right now, one formula for the LATE status is

Please Login or Register  to view this content.

but I don't understand the B4 part because that does not say Received, and A5 is not a number, ever.

---

Restating just in case it helps. worksheet one is what I have, worksheet two is what I want. I want columns "Sent" and "Received" to stay tied together and I want to add two columns in between them. "Appeal Rec'd" and "Appeal Resp" that are tied to each other, but NOT tied to the "Sent" and "Received" columns.