Condition formatting based on two different outcomes

[andyp24]

Hi all, I hope you can help

excel formula1.jpg

Column A, date I visit the customer.
Column B, the date the customer must have returned documentation 10 workdays before the appointment date
Column C, the date the customer actually returned the documentation.

I need a condition formula which will turn the cells in column B Red if the customer hasn't returned documentation 10 days before the appointment date. However, i need that formula to be superceded if Column C receives a returned date which will then turn the cell Green. (example column B3 and B4).

I dare say its an IF function but i cant seem to get it right

Thanks all

Andy

[WideBoyDixon]

Highlight B2 down as far as required and use the following formulas to create two conditional formatting rules:

Please Login or Register  to view this content.

For the first one, set the format to be red background and bold text. For the second one, set the format to be green background. See attached for an example.

WBD

[andyp24]

Hi WBD,

That's magic, thank you very much. I would never have resolved this without your help, thanks pal.

Andy

[andyp24]

Hi WBD,

Ive just noticed, when I run this condition rule in my spreadsheet, it also turns cells Red for those rows which are yet to be appointed in column A? Ideally I'd like the cell to stay clear of formatting until an appointment date is set in column A.


For example, I have 50 sites, 25 of which have been appointed and 25 yet to be appointed. Those which have been appointed in column A will either turn Red or Green depending on documents being received or not. For the remaining 25 sites which are yet to receive an appointment, column B will remain blank of colour until an appointment date is set.

Thanks pal

Andy

[WideBoyDixon]

Change the first condition to this then:

Please Login or Register  to view this content.

WBD

[andyp24]

That's the puppy, thank you again WBD :-)