# Excel Conditional Formatting if another column contains data

So I have a worksheet which has three date columns,

Column B is received date

Column D is due date

Column F is response date

Column D uses the formula

=WORKDAY(Bx,\$S\$1)

where S1 just says 10, such that column D displays the due date as 10 working days from the received date of column B.

I have then conditionally formatted column D to highlight dates that are past due with

cell value < NOW()

What I would like to do is once there is a response date in column F, the dates in column D are no longer appearing red.

Is this possible? Column F will be blank until the date is input manually but I can’t get ISBLANK to do anything

• please have a look at the answer below if it meets the requirement Commented Nov 9, 2020 at 9:08

You can change your conditional formatting rule to use:

``````=AND(\$F2<>"",\$D2<TODAY())
``````

`AND` simply lets you tie together more than one condition such that if both are `TRUE` then the result of the formula is also `TRUE`.

Step 1 Create a random set of dates — received date

and its related days — Mon to Sun

The formula used in C3 is =TEXT(B3,"ddd")

At the same time an arbitrary set of holidays is also entered in I3 and I4

Step 2 create the due dates — by adding the chosen number of days — in this case 10 days in the future ￼

and add the related days — Mon to Sun

The formula used in D3 is =WORKDAY(B3,\$M\$3,holidays)

Step 3 add an arbitrary response date — in this case 15 Nov 2020

Step 4 it can be seen that the highlighting / conditional formatting will apply onto D3 and not D4 — since there is a response date in front of D4 — the conditional formatting applies to only D3 since there is no response date in front of D3 and also since the date in D3 is less than the check date

The check date can be altered to =TODAY() In the present case an arbitrary check date is used as 19 Nov 2020 — in M4

Step 5 the formulas are shown in excel as under

• It is not clear what the random set of dates is for. This distracts from the answer. Recommend revising the answer so only that part necessary to answer the original posters question is included. Commented Nov 21, 2020 at 23:30
• @RSchultz my answer was in the same format as the question by the OP -- that is a step wise explanation starting from the first data column onwards -- still waiting for an input from the OP if it meets the requirement -- willing to help further refine the solution -- the final conditional formula is also entered Commented Nov 22, 2020 at 1:57