Hi,

Column A has date, Column B has number of days required to finish the task,
in Column C I want to use a formula which adds Column B( number of days) to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

Set up a list of holiday dates to be excluded. Assume this list is in the
range J1:J10.

Then, use this formula in column C:

=WORKDAY(A1,B1,J1:J10)

The WORKDAY function requires the Analysis ToolPak add-in be installed for
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish the
task,
in Column C I want to use a formula which adds Column B( number of days)
to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

NM wrote:
Hi,

Column A has date, Column B has number of days required to finish the task,
in Column C I want to use a formula which adds Column B( number of days) to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.


Look at the NETWORKDAYS() function.

Glenn wrote:
NM wrote:
Hi,

Column A has date, Column B has number of days required to finish the
task, in Column C I want to use a formula which adds Column B( number
of days) to column A but excludes holidays and weekends and adds only
working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be
12/17/08 ( only working days to be added)

Thanks for your help.


Look at the NETWORKDAYS() function.


As Biff pointed out, WORKDAYS() is the correct function for your task.

Try this

=WORKDAY(A1,B1,Holidays)

Holidays is a named rtange that contains any holidaya dates.

If you get a NAME# error then

Tools|addins and load the analysis toolpak.

Mike

"NM" wrote:

Hi,

Column A has date, Column B has number of days required to finish the task,
in Column C I want to use a formula which adds Column B( number of days) to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

Forgot to mention:

WORKDAY returns the date serial number so you'll have to format the formula
cells as Date.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Set up a list of holiday dates to be excluded. Assume this list is in the
range J1:J10.

Then, use this formula in column C:

=WORKDAY(A1,B1,J1:J10)

The WORKDAY function requires the Analysis ToolPak add-in be installed for
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish the
task,
in Column C I want to use a formula which adds Column B( number of days)
to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08
(
only working days to be added)

Thanks for your help.

On Tue, 9 Dec 2008 11:08:12 -0800, NM wrote:

Hi,

Column A has date, Column B has number of days required to finish the task,
in Column C I want to use a formula which adds Column B( number of days) to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

Check HELP for the WORKDAY function.

If you are using a version of Excel prior to 2007, read HELP carefully for the
"fix" in case you get a #NAME! error when trying the function.

=WORKDAY(A1,B1,[holidays])

Holidays is an optional argument which is an array of holiday dates. So you
might have them listed in some range somewhere.
--ron

Use the WORKDAY function. Look it up in Excel help.
--
David Biddulph

"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish the
task,
in Column C I want to use a formula which adds Column B( number of days)
to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

What is the easiest way to set up a list of weekends and holidays?

"T. Valko" wrote:

Set up a list of holiday dates to be excluded. Assume this list is in the
range J1:J10.

Then, use this formula in column C:

=WORKDAY(A1,B1,J1:J10)

The WORKDAY function requires the Analysis ToolPak add-in be installed for
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish the
task,
in Column C I want to use a formula which adds Column B( number of days)
to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

You don't need to list the weekend dates, the WORKDAY function knows to
exclude those but you have to list any holiday dates that you want to
exclude from the calculation. You simply enter those dates in a range of
cells:

J1: 1/1/2009
J2: 1/2/2009
J3: 2/21/2009
J4: 5/31/2009
J5: 7/4/2009
etc
etc
etc


--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
What is the easiest way to set up a list of weekends and holidays?

"T. Valko" wrote:

Set up a list of holiday dates to be excluded. Assume this list is in the
range J1:J10.

Then, use this formula in column C:

=WORKDAY(A1,B1,J1:J10)

The WORKDAY function requires the Analysis ToolPak add-in be installed
for
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish the
task,
in Column C I want to use a formula which adds Column B( number of
days)
to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be
12/17/08 (
only working days to be added)

Thanks for your help.

Hello,

One example for US bank holidays:
http://www.sulprobil.com/html/holiday_table.html

Regards,
Bernd

I am still getting NAME # error!

"Mike H" wrote:

Try this

=WORKDAY(A1,B1,Holidays)

Holidays is a named rtange that contains any holidaya dates.

If you get a NAME# error then

Tools|addins and load the analysis toolpak.

Mike

"NM" wrote:

Hi,

Column A has date, Column B has number of days required to finish the task,
in Column C I want to use a formula which adds Column B( number of days) to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be 12/17/08 (
only working days to be added)

Thanks for your help.

How can I use the 'Workday' function with 'If' statement.
eg. If column A is empty then it should pick up the date from column c, if
column C is empty is should pick up the date which is in column D and the
same for column E?

Thanks for you help.

"T. Valko" wrote:

You don't need to list the weekend dates, the WORKDAY function knows to
exclude those but you have to list any holiday dates that you want to
exclude from the calculation. You simply enter those dates in a range of
cells:

J1: 1/1/2009
J2: 1/2/2009
J3: 2/21/2009
J4: 5/31/2009
J5: 7/4/2009
etc
etc
etc


--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
What is the easiest way to set up a list of weekends and holidays?

"T. Valko" wrote:

Set up a list of holiday dates to be excluded. Assume this list is in the
range J1:J10.

Then, use this formula in column C:

=WORKDAY(A1,B1,J1:J10)

The WORKDAY function requires the Analysis ToolPak add-in be installed
for
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish the
task,
in Column C I want to use a formula which adds Column B( number of
days)
to
column A but excludes holidays and weekends and adds only working days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be
12/17/08 (
only working days to be added)

Thanks for your help.

Try this:

=WORKDAY(IF(A1="",IF(C1="",IF(D1="",E1,D1),C1),A1) ,B1,J1:J10)


--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
How can I use the 'Workday' function with 'If' statement.
eg. If column A is empty then it should pick up the date from column c, if
column C is empty is should pick up the date which is in column D and the
same for column E?

Thanks for you help.

"T. Valko" wrote:

You don't need to list the weekend dates, the WORKDAY function knows to
exclude those but you have to list any holiday dates that you want to
exclude from the calculation. You simply enter those dates in a range of
cells:

J1: 1/1/2009
J2: 1/2/2009
J3: 2/21/2009
J4: 5/31/2009
J5: 7/4/2009
etc
etc
etc


--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
What is the easiest way to set up a list of weekends and holidays?

"T. Valko" wrote:

Set up a list of holiday dates to be excluded. Assume this list is in
the
range J1:J10.

Then, use this formula in column C:

=WORKDAY(A1,B1,J1:J10)

The WORKDAY function requires the Analysis ToolPak add-in be installed
for
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Column A has date, Column B has number of days required to finish
the
task,
in Column C I want to use a formula which adds Column B( number of
days)
to
column A but excludes holidays and weekends and adds only working
days.

eg. Column A has 12/8/08 Column B has 7 days coulmn C should be
12/17/08 (
only working days to be added)

Thanks for your help.