0

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

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

2 Answers 2

0

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.

0

Step 1 Create a random set of dates — received date

enter image description here

and its related days — Mon to Sun

enter image description here

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

enter image description here

Step 2 create the due dates — by adding the chosen number of days — in this case 10 days in the future  enter image description here

and add the related days — Mon to Sun

enter image description here

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

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

enter image description here

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

enter image description here

enter image description here

2
  • 1
    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.
    – R Schultz
    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
    – js bibra
    Commented Nov 22, 2020 at 1:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .