Conditional Formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gav123 - 14 Jun 2007 08:45 GMT

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.

Reply to this Message

Mike H - 14 Jun 2007 09:44 GMT

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

> Hi,
>
[quoted text clipped - 9 lines]
>
> Gav.

Reply to this Message

Gav123 - 14 Jun 2007 09:49 GMT

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.

> You need 2 conditional formats in D
>
[quoted text clipped - 20 lines]
> >
> > Gav.

Reply to this Message

Mike H - 14 Jun 2007 10:46 GMT

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

> Hi Mike,
>
[quoted text clipped - 29 lines]
> > >
> > > Gav.

Reply to this Message

Gav123 - 14 Jun 2007 12:59 GMT

Thanks Mike..Worked like a charm...

> Gavin,
>
[quoted text clipped - 45 lines]
> > > >
> > > > Gav.

Reply to this Message