Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

On Sun, 27 May 2007 22:39:13 +0200, "Erny"
wrote:

Hi Ron,

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

Kind regards,
Erny

But Erny, NETWORKDAYS will not work in the conditional formatting dialog box in
versions for Excel prior to 2007.
--ron

Conditional formatting to exclude weekend and Bank Holidays
 

Erny and Ron

Thank you both for your help

Regards

Paul


"Ron Rosenfeld" wrote in message
...
On Sun, 27 May 2007 22:39:13 +0200, "Erny"
wrote:

Hi Ron,

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

Kind regards,
Erny

But Erny, NETWORKDAYS will not work in the conditional formatting dialog
box in
versions for Excel prior to 2007.
--ron

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

On Mon, 28 May 2007 16:27:05 +0100, "Paul"
wrote:

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

mocTODenilnodlefnesorTAnor

Reverse the above, then make the obvious substitutions.
--ron

Conditional formatting to exclude weekend and Bank Holidays
 

Hi Ron,

I have sent you an e-mail. If you have not received it, then please send me
one and I will return it with the spreadsheet.

Thanks

Paul

"Ron Rosenfeld" wrote in message
...
On Mon, 28 May 2007 16:27:05 +0100, "Paul"
wrote:

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

mocTODenilnodlefnesorTAnor

Reverse the above, then make the obvious substitutions.
--ron

Conditional formatting to exclude weekend and Bank Holidays
 

On Mon, 28 May 2007 21:29:22 +0100, "Paul"
wrote:

Hi Ron,

I have sent you an e-mail. If you have not received it, then please send me
one and I will return it with the spreadsheet.

Thanks

Paul

"Ron Rosenfeld" wrote in message
.. .
On Mon, 28 May 2007 16:27:05 +0100, "Paul"
wrote:

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

mocTODenilnodlefnesorTAnor

Reverse the above, then make the obvious substitutions.
--ron


Response is on its way back to you.
--ron