Hi All

I am trying to use conditional formatting to show the difference between a
due date and a forecast date. For example, if the forecast date is up to 5
days from the due date the cell is green, if is between 6 to 10 days it
turns yellow and if it is 11 days or more it turns red. How can I
conditionally format so Saturday & Sunday (and Bank Holidays?) aren't
figured in as days?

Any help would be appreciated

Thanks

Paul

Try using the function WORKDAYS(<due date, <forecast date,<holidays).

Ex. for the conditional formatting, if your forecast date is in cell A1, due
date in B1 and holidays in cells C1:C20:
Condition 1 for "red": use formula
=WORKDAYS(B1,A1,$C$1:$C$20)11

You can use similar formulas for the other colours.

I use a french language version, hope the english equivalent is correct as
displayed.

Kind regards,
Erny

"Paul" schrieb im Newsbeitrag
...
Hi All

I am trying to use conditional formatting to show the difference between a
due date and a forecast date. For example, if the forecast date is up to 5
days from the due date the cell is green, if is between 6 to 10 days it
turns yellow and if it is 11 days or more it turns red. How can I
conditionally format so Saturday & Sunday (and Bank Holidays?) aren't
figured in as days?

Any help would be appreciated

Thanks

Paul

On Sat, 26 May 2007 20:31:20 +0200, "Erny"
wrote:

Try using the function WORKDAYS(<due date, <forecast date,<holidays).

Ex. for the conditional formatting, if your forecast date is in cell A1, due
date in B1 and holidays in cells C1:C20:
Condition 1 for "red": use formula
=WORKDAYS(B1,A1,$C$1:$C$20)11

You can use similar formulas for the other colours.

I use a french language version, hope the english equivalent is correct as
displayed.

Kind regards,
Erny


Did you try out your recommendation? That's always a useful thing to do.

In Excel 2003, there is no WORKDAYS function and I don't think it is present in
2007.

If you meant NETWORKDAYS, it won't work as a conditional formatting formula in
Excel 2003 since it refers to another workbook (the VBA add-in: atpvbaen)
--ron

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Regards

Paul
"Ron Rosenfeld" wrote in message
...
On Sat, 26 May 2007 20:31:20 +0200, "Erny"
wrote:

Try using the function WORKDAYS(<due date, <forecast date,<holidays).

Ex. for the conditional formatting, if your forecast date is in cell A1,
due
date in B1 and holidays in cells C1:C20:
Condition 1 for "red": use formula
=WORKDAYS(B1,A1,$C$1:$C$20)11

You can use similar formulas for the other colours.

I use a french language version, hope the english equivalent is correct as
displayed.

Kind regards,
Erny


Did you try out your recommendation? That's always a useful thing to do.

In Excel 2003, there is no WORKDAYS function and I don't think it is
present in
2007.

If you meant NETWORKDAYS, it won't work as a conditional formatting
formula in
Excel 2003 since it refers to another workbook (the VBA add-in: atpvbaen)
--ron

On Sun, 27 May 2007 15:14:51 +0100, "Paul"
wrote:

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Not including holidays, the following formula should work to compute the number
of working days between two dates. A1 is the earlier date, B1 the later.

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

Similar to the NETWORKDAYS function, this formula counts the starting date.

Another option would be to use the NETWORKDAYS function in a hidden column, and
then have your conditional formatting formula reference the contents of that
hidden column.

e.g.

=NETWORKDAYS(forecast_date,due_date,holidays)

Then, in your conditional formattinge:

Formula Is: =cell_ref=11 (red)
Formula Is: =cell_ref= 6 (yellow)

Set the baseline color to be green.
--ron

Hi Ron,

Thanks for the precision, so it was NETWORKDAYS the function I should have
quoted for the English version.

Kind regards,
Erny

"Ron Rosenfeld" schrieb im Newsbeitrag
...
On Sun, 27 May 2007 15:14:51 +0100, "Paul"
wrote:

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Not including holidays, the following formula should work to compute the
number
of working days between two dates. A1 is the earlier date, B1 the later.

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

Similar to the NETWORKDAYS function, this formula counts the starting
date.

Another option would be to use the NETWORKDAYS function in a hidden
column, and
then have your conditional formatting formula reference the contents of
that
hidden column.

e.g.

=NETWORKDAYS(forecast_date,due_date,holidays)

Then, in your conditional formattinge:

Formula Is: =cell_ref=11 (red)
Formula Is: =cell_ref= 6 (yellow)

Set the baseline color to be green.
--ron

Hi Ron,

Thanks for your help but I could not get your suggestion to work correctly.
For example the 'Sum' formula that compares the set date and the forecast
date, when these two dates are the same, the result is 1 when I would expect
as answer if 0. Also when I enter your 'Formula is' into the conditional
formatting the cells do not change colour.

I'm obvously doing something wrong. Is there anyway I can send you my
examaple spreadsheet so that you can enter this information directly?

One other item that I forgot to mention. When a task is complete the cell
needs to be blue and contains the completion date. I don't know if there is
anyway that can be worked into the formula as well?

Thanks for your help

Paul


"Ron Rosenfeld" wrote in message
...
On Sun, 27 May 2007 15:14:51 +0100, "Paul"
wrote:

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Not including holidays, the following formula should work to compute the
number
of working days between two dates. A1 is the earlier date, B1 the later.

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

Similar to the NETWORKDAYS function, this formula counts the starting
date.

Another option would be to use the NETWORKDAYS function in a hidden
column, and
then have your conditional formatting formula reference the contents of
that
hidden column.

e.g.

=NETWORKDAYS(forecast_date,due_date,holidays)

Then, in your conditional formattinge:

Formula Is: =cell_ref=11 (red)
Formula Is: =cell_ref= 6 (yellow)

Set the baseline color to be green.
--ron

Hi Ron,

Thanks for your help but I could not get your suggestion to work correctly.
For example the 'Sum' formula that compares the set date and the forecast
date, when these two dates are the same, the result is 1 when I would expect
as answer if 0. Also when I enter your 'Formula is' into the conditional
formatting the cells do not change colour.

I'm obvously doing something wrong. Is there anyway I can send you my
examaple spreadsheet so that you can enter this information directly?

One other item that I forgot to mention. When a task is complete the cell
needs to be blue and contains the completion date. I don't know if there is
anyway that can be worked into the formula as well?

Appreciate your help

Paul

"Ron Rosenfeld" wrote in message
...
On Sun, 27 May 2007 15:14:51 +0100, "Paul"
wrote:

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Not including holidays, the following formula should work to compute the
number
of working days between two dates. A1 is the earlier date, B1 the later.

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

Similar to the NETWORKDAYS function, this formula counts the starting
date.

Another option would be to use the NETWORKDAYS function in a hidden
column, and
then have your conditional formatting formula reference the contents of
that
hidden column.

e.g.

=NETWORKDAYS(forecast_date,due_date,holidays)

Then, in your conditional formattinge:

Formula Is: =cell_ref=11 (red)
Formula Is: =cell_ref= 6 (yellow)

Set the baseline color to be green.
--ron