To simplify my question, say I have two columns, one with date's (expected task finish date) and one with % complete:

(Example: D2: 10/06/2019; E2: 85%)

(Example: D3: 10/12/2019; D3: 75%)

(Assume today's date is 10/07/2019).

How can I use conditional formatting so that any cell in column D would have different background/font color based on the planned finish date in relation to today's date and in relation to task completion percentage?

The criteria we'd like to use:

1) If task target finish < today's date AND % < 100%, turn cells red

2) If task target finish is WITHIN 2 weeks of today's date AND % < 90%, turn cells orange.

Any help with this where I could use formatting and not need some other column would be so amazing!!