Conditional Formatting

[Guest]

Hi,

Here is my problem, I have a list of dates of when a product was shipped in
column C and the date when it was received in column D. I would like the date
in D to be highlighted red when it is more than 3 working days from the date
in C and green when it's less than or equal to 3 working days. I would also
like the rest of the cells in D to be white until a date is entered.

Any help and advice that you can offer would be appreciated.

Thanks,

Gav.

 

[Guest]

You need 2 conditional formats in D

First. Cell value is - less than or equal to =$C$2+3 and set the colour to
green

Click ADD

Second. Cell value is - Greater than =$C$2+3 and set the colour to red.

Mike

 

[Guest]

Hi Mike,

Thanks for the quick response.

I need the formatting to work on week days only as we don't include the
weekends.

Gav.

 

[Guest]

Gavin,

In a cell (say E1) alongside your first row enter the formula

=SUM(INT((WEEKDAY(C1-{2,3,4,5,6})+D1-C1)/7))

The will calculate the workdays beytween c1 and d1

Now enter two conditional formats.
1. =E1<=3 and set green
2. =E1 >3 and set Red

You can drag down the formula in E1 for additional cells.

Mike

 

[Guest]

Thanks Mike..Worked like a charm...

Gavin,

In a cell (say E1) alongside your first row enter the formula

=SUM(INT((WEEKDAY(C1-{2,3,4,5,6})+D1-C1)/7))

The will calculate the workdays beytween c1 and d1

Now enter two conditional formats.
1. =E1<=3 and set green
2. =E1 >3 and set Red

You can drag down the formula in E1 for additional cells.

Mike