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(or any other way) so Saturday & Sunday, and if possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Paul, the simplest way would be to have helper cells where 5 days, 10 days
forward was calculated using the WORKDAY function, and test these in the CF.
Unfortunately you cannot use WORKDAY in CF directly.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul
"Bob Phillips" wrote in message
...
Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Look at my first post then and try that method.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Paul" wrote in message
...
Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul
"Bob Phillips" wrote in message
...
Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Hi Bob,

I do not know how to do this. An example would be most helpful.

Thanks

Paul
"Bob Phillips" wrote in message
...
Look at my first post then and try that method.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Paul" wrote in message
...
Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul
"Bob Phillips" wrote in message
...
Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Paul,

Give me an example of your data, and I will knock you up an example.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Paul" wrote in message
...
Hi Bob,

I do not know how to do this. An example would be most helpful.

Thanks

Paul
"Bob Phillips" wrote in message
...
Look at my first post then and try that method.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Paul" wrote in message
...
Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul
"Bob Phillips" wrote in message
...
Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Hi Bob

Please find attached an example of the spreadsheet.

As you can see, it is a simple spreadsheet comparing baseline and forecast
dates. Hopefully the forecast date cells will change colour depending on
what date the user enters if beyond the baseline date. Only exclude the
weekend dates in the calculations if bank holidays are to difficult to
exclude as well.

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?

Your help is much appreciated

Thanks

Paul

"Bob Phillips" wrote in message
...
Paul,

Give me an example of your data, and I will knock you up an example.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Paul" wrote in message
...
Hi Bob,

I do not know how to do this. An example would be most helpful.

Thanks

Paul
"Bob Phillips" wrote in message
...
Look at my first post then and try that method.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Paul" wrote in message
...
Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul
"Bob Phillips" wrote in message
...
Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

Hi Bob

I have sent you a copy of the spreadsheet to your e-mail address as I can't
seem to post a reply with an attachment.

Regards

Paul
"Bob Phillips" wrote in message
...
Paul,

Give me an example of your data, and I will knock you up an example.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Paul" wrote in message
...
Hi Bob,

I do not know how to do this. An example would be most helpful.

Thanks

Paul
"Bob Phillips" wrote in message
...
Look at my first post then and try that method.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Paul" wrote in message
...
Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul
"Bob Phillips" wrote in message
...
Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Paul" wrote in message
...
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(or any other way) so Saturday & Sunday, and if
possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul