Conditional formatting

[Jared14h]

Evening everyone.

I am after some help please. In cell A1 I have a date by which a report is due. In cell B1, I want to enter the date when the report has been completed.

I want the cell in A1 to be formatted to be green if B1 date is prior to A1, red if after A1 and orange if coming up to the date I.e. due in 30 days.

This all seems relatively easy. However the problem I am having is that cell A1 is green even when B1 is yet to have the completion date entered. Until there is a date in B1, I would like A1 to have no formatting, but retain its date.

Also, I want the green formatting to override the orange if/when that date in B1 has been entered.

These seem like reasonably easy things to do in excel, however I just can’t seem to do it!

Any help here would be great. Thank you

[Sam Capricci]

I would do it this way...
go to conditional formatting >> new rule >> formulas only cells that contain >> format only cells with: cell value less than =B1 and select the format fill as yellow, then ok and ok.
Then open CF again and do new rule >> formulas only cells that contain >> format only cells with: cell value greater than or equal to =B1 and select the format fill as green, then ok and ok.

EDIT: where did I get yellow? need more caffeine, you can adjust mine as needed or just go with Marcilio's solution, it looks right, difference is he is using formulas, just make sure you keep the rules in the correct order to get the right results.

[Mazzaropi]

jared14h, Good afternoon.

Try to do:

Select Range A1
Menu Conditional Format; Using formula

Rule 1
Type =AND(B1<>"",B1<A1)
Choose Fill color as GREEN
OK

Rule 2
Type =AND(B1="",(A1-TODAY())<=30)
Choose Fill color as ORANGE
OK

Rule 3
Type =AND(B1<>"",B1>A1)
Choose Fill color as RED
OK

Please, tell us if it worked as desired.
I hope it helps.