Weekday Conditional Formatting

[danesrood]

Dear All

I have a date in B4 with the following formula in C4: =IF(A4="","",(WORKDAY(A4,10))) as we have to action something within 10 working days

In C4 I would like to have some CF incorporating the workday function which I suppose would be refreshed when the worksheet is accessed.

If the due date in C4 is greater than or equal to 5 workdays away - green
then orange for 3-4 and red for 1-2

I hope that I have explained the situation and any help you can provide would be most appreciated.

[Bob Phillips]

As you have the formula in B4, just add CF with a condition of cell value >= 5

[danesrood]

xld

Surely that won't work because the formula in B4 brings in a date which is 10 working days from the date entered into A4.

Or have I misunderstandood your reply.

[Bob Phillips]

You'r right, I misread it, What is your Excel version.

[danesrood]

Its currently 2002 !!

[Bob Phillips]

Then try a CF formula of

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&B4)))<>1),(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&B4)))<>7)))*(1-2*(TODAY()>B4))>5

[danesrood]

xld

That works fine thank you.

My word what a complicated formula, no wonder I couldn't get it to work.

Are you able to please provide similar for 3-4 and 1-2 working days.

Again my grateful thanks for what you have already done.

[Bob Phillips]

Just change the > 5 to the appropriate number.

[danesrood]

xld

I did try that yesterday but it didn't seem to work but today it does - hmm obviously not concentrating.

Anyway, its a fabulous formula which will be of great help so many thanks for taking the time to help me out.

[Bob Phillips]

ACtually, we can simplify it

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&B4)),2)<6))*(1-2*(TODAY()>B4))>5

[Bob Phillips]

Or a bit more

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&B4)),2)<6))*(TODAY()<=B4)>5

[danesrood]

xld

Again my sincere thanks for working this out for me.

I can't pretend to understand what is being done here so I wonder if you could have a try at explaining it to me especially the use of ROW.

Many thanks

[Bob Phillips]

In essence, the formula works on two dates, TODAY and B4. It needs to create an array of all dates between those two that WEEKDAY can interrogate, the result of which SUMPRODUCT sums. The INDIRECT creates a string that ROW transforms into an array of date numbers

For instance, suppose that B4 holds 17th Feb. Then ROW(INDIRECT(TODAY()&":"&B4)) creates an array of these numbers {40224;40225;40226}, which is the number values for 15th, 16th and 17th Feb.

WEEKDAY then works on this array to return an array of weekday numbers. I use the ,2 argument so as to reduce the formula, by using WEEKDAY with the week starting on Monday. In the earlier example, WEEKDAY(ROW(INDIRECT(TODAY()&":"&B4)),2) returns an array {1;2;3}.

This array is tested against being less than 6 as weekend days are 6 and 7, so testing against 6 returns an array of TRUE or FALSE for each daya, TRUE for weekdays, FALSE for wekend days. WEEKDAY(ROW(INDIRECT(TODAY()&":"&B4)),2) returns {TRUE;TRUE;TRUE}.

The -- just transforms TRUE/FALSE values to 1. SUMPRODUCT then adds this array of 1/0.

Thus, the SUMPRODUCT part calculates the number of working days between today and B4. The *(TODAY()<=B4) is just to make sure that the situation where TODAY is greater than B4 returns 0, so as to discount past dates.

Finally, the full result is compared against the threshold to give TRUE or FALSE which conditional formatting likes.