I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!

Use: =IF(OR(WEEKDAY(TODAY()+30)=1,WEEKDAY(TODAY()+30)=7 ),TODAY()+31,TODAY()+30)

Where a WEEKDAY value of 1 = SUNDAY and a WEEKDAY value of 7 = Saturday.

As for national holiday--that's dependent on the country you live in.
Perhaps look at the NETWORKDAYS function if that's really a requirement.

Dave
--
Brevity is the soul of wit.


"Andrew Mackenzie" wrote:

I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!

On Wed, 3 Jan 2007 11:24:58 -0000, "Andrew Mackenzie"
wrote:

I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!



=WORKDAY(A1+29,1,holidays)

Holidays is a named range with a list of your holidays.

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

Thanks very much Dave and Ron.

Ron, yours seems a prettier solution and it seems to have worked with a
brief test. However, I am curious to know why we add 29 rather than 30 to
my start date. Can you enlighten me?

Thanks,
Andrew.
"Andrew Mackenzie" wrote in message
...
I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!

With A1:A4 holding:

11/27/2006
11/28/2006
11/29/2006
11/30/2006

When I enter in B1 and Copy down

=WORKDAY(A1+29,1,Holidays)

I get in B1:B4:

12/27/2006
12/28/2006
12/29/2006
01/01/2007 <<< Not sure why this one?? Any ideas what's going wrong?

My Holiday range only includes 10/31/2006 and 12/25/2006 (2 cells)

Thanks,





"Ron Rosenfeld" wrote in message
:

On Wed, 3 Jan 2007 11:24:58 -0000, "Andrew Mackenzie"
wrote:

I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!



=WORKDAY(A1+29,1,holidays)

Holidays is a named range with a list of your holidays.

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

Never mind; additional testing is revealing an original
misunderstanding;;;
Thanks,


"JMay" wrote in message
:

With A1:A4 holding:

11/27/2006
11/28/2006
11/29/2006
11/30/2006

When I enter in B1 and Copy down

=WORKDAY(A1+29,1,Holidays)

I get in B1:B4:

12/27/2006
12/28/2006
12/29/2006
01/01/2007 <<< Not sure why this one?? Any ideas what's going wrong?

My Holiday range only includes 10/31/2006 and 12/25/2006 (2 cells)

Thanks,





"Ron Rosenfeld" wrote in message
:

On Wed, 3 Jan 2007 11:24:58 -0000, "Andrew Mackenzie"
wrote:

I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!



=WORKDAY(A1+29,1,holidays)

Holidays is a named range with a list of your holidays.

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

Andrew--not only is Ron's suggestion prettier, it also is easier to audit in
the future. Definitely go with it instead of my response.

Dave
--
Brevity is the soul of wit.


"Andrew Mackenzie" wrote:

Thanks very much Dave and Ron.

Ron, yours seems a prettier solution and it seems to have worked with a
brief test. However, I am curious to know why we add 29 rather than 30 to
my start date. Can you enlighten me?

Thanks,
Andrew.
"Andrew Mackenzie" wrote in message
...
I would appreciate any help that anyone can give with this problem.

I have a column of dates and next to it I want to create a formula which
adds 30 calendar days and if the result is a weekend or national holiday
then the result I want is the next good working day.

Help please!

On Wed, 3 Jan 2007 12:42:03 +0000, "JMay" wrote:

With A1:A4 holding:

11/27/2006
11/28/2006
11/29/2006
11/30/2006

When I enter in B1 and Copy down

=WORKDAY(A1+29,1,Holidays)

I get in B1:B4:

12/27/2006
12/28/2006
12/29/2006
01/01/2007 <<< Not sure why this one?? Any ideas what's going wrong?

My Holiday range only includes 10/31/2006 and 12/25/2006 (2 cells)

Thanks,



If you don't have 1 Jan listed in your Holiday range, and it is not a weekend
day (Saturday Sunday), Excel cannot know that you want to consider it a
holiday.

1 Jan 2007 was a Monday.

--ron

On Wed, 3 Jan 2007 12:03:07 -0000, "Andrew Mackenzie"
wrote:

Thanks very much Dave and Ron.

Ron, yours seems a prettier solution and it seems to have worked with a
brief test. However, I am curious to know why we add 29 rather than 30 to
my start date. Can you enlighten me

The extra "1" day is added by the WORKDAY function.

If you first add 29 days, ignoring weekends and holidays, to your initial date
and then add "1" Workday, the WORKDAY function will skip over that next date if
it is a weekend or listed in your list of Holidays.

That was how I understood your requirements.


--ron