Sum working days

[NM]

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.

 

[T. Valko]

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.

 

[Glenn]

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]

Look at the NETWORKDAYS() function.

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

 

[Mike H]

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

 

[T. Valko]

Forgot to mention:

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

 

[Ron Rosenfeld]

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

 

[NM]

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

 

[T. Valko]

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

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[NM]

I am still getting NAME # error!

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]

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]

Try this:

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