Working days

[Andrew Mackenzie]

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!

 

[Ron Rosenfeld]

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 Mackenzie]

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.

 

[JMay]

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,

 

[JMay]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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