Conditional format of date where 3 working days greater than cell value

1
votes

I am trying to adapt a number of forum posts and previous stack questions like this.

Let's say I have a date Column A2, A3, A4 etc.

I want to be able to utilize the WEEKDAY function to evaluate my condition when only in a Weekday and also 3 days greater than the date in Cell A(x).

So far my attempts have failed or I have not got the results I want. Excel is not my strongest point when it comes to conditional formula formatting.

I think it may be something like:

=WEEKDAY(A$2,2) <=5 AND 'My Cell Date' > 3 Days WHERE those 3 days are weekdays.

Also if it is possible, I want it to work like:

If day 1 of 3 is Thursday, day 2 of 3 is Friday and day 3 of 3 is Monday then apply formatting.

Could anyone help me out with this one please?

Edit not from OP to copy clarification from comments:

  • If the today date is 3 working days greater than the cell date change to red.
  • Say A2 has a received date and B2 has a completed date. The completed date is my target. Is it 3 working days greater than received.
1
excelexcel-formulaexcel-2010worksheet-functionconditional-formatting
Basically if this evaluates as true then I want to set the colour of the cell. Thanks for taking the time the comment. If the today date is 3 working days greater than the cell date change to red – dotnetnewb
Ok, lets say A2 has a received date and B2 has a completed date. The completed date is my target. Is it 3 working days greater than received. I was trying to keep as simple as possible. Sorry if not clear. – dotnetnewb
Clearly missed that one. Please submit answer and you can have it. I must have been having a moment or something..... – dotnetnewb

1 Answers

1
votes

Was not quite certain of the requirement (but seem to have hit upon an acceptable solution!):

Use =NETWORKDAYS(A2,B2)>2 under Format values where this formula is true: under Use a formula to determine which cells to format in Conditional Formatting.

NETWORKDAYS

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.

The syntax is:

NETWORKDAYS(start_date,end_date,holidays)

Dates should be entered by using the DATE function, or as results of other formulas or functions.