Count the Number of Workdays
 

Trying to figure how I can take a start and end date and count the number of
work days based on a Monday through Thursday work schedule. Does NetWorkdays
function have a special operative that will enable me to perform the above
condition.

Count the Number of Workdays
 

One way...

A1 = start date
B1 = end date

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

--
Biff
Microsoft Excel MVP


"Rocetman" wrote in message
...
Trying to figure how I can take a start and end date and count the number
of
work days based on a Monday through Thursday work schedule. Does
NetWorkdays
function have a special operative that will enable me to perform the above
condition.

Count the Number of Workdays
 

I have a number of formulas on my web site that perform a task similar
to NETWORKDAYS but allow you to excude any number of days of the week
from the calculation. One set of formulas is simple but doesn't
support a list of holidays to exclude. The other set is more
complicated but allows a list of holidays to exclude. These functions
do not require NETWORKDAYS or the Analysis ToolPak. See
http://www.cpearson.com/excel/BetterNetworkDays.aspx for details and
examples.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Thu, 25 Feb 2010 15:02:03 -0800, Rocetman
wrote:

Trying to figure how I can take a start and end date and count the number of
work days based on a Monday through Thursday work schedule. Does NetWorkdays
function have a special operative that will enable me to perform the above
condition.

Count the Number of Workdays
 

Some clarification...

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

{1,2,3,4} represents the weekday numbers that you want the count for.

Whe

1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

A1 = start date
B1 = end date

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

--
Biff
Microsoft Excel MVP


"Rocetman" wrote in message
...
Trying to figure how I can take a start and end date and count the number
of
work days based on a Monday through Thursday work schedule. Does
NetWorkdays
function have a special operative that will enable me to perform the
above
condition.

Count the Number of Workdays
 

"T. Valko" wrote:

Some clarification...

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

{1,2,3,4} represents the weekday numbers that you want the count for.

Whe

1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

A1 = start date
B1 = end date

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

--
Biff
Microsoft Excel MVP


"Rocetman" wrote in message
...
Trying to figure how I can take a start and end date and count the number
of
work days based on a Monday through Thursday work schedule. Does
NetWorkdays
function have a special operative that will enable me to perform the
above
condition.




.
This helps a great deal, but is there a way to exclude the major holidays such as Christmas, New Years, Labor Day, Thanksgiving, Memorial Day, and 4th of July. Even though this would be a simple task for one year by setting up a string in a seperate column, but I would need to count for several years since the job that I am on would last at least 10 years. Have any suggestions that Excel could automatically exclude the above holidays in "count the Number of workdays"?

Count the Number of Workdays
 

Excluding holidays complicates things a bit!

Let's try a different formula for that.

A1 = start date
B1 = end date
C1:C10 = list of holiday DATES to be excluded

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C10,0))))

--
Biff
Microsoft Excel MVP


"Rocetman" wrote in message
...


"T. Valko" wrote:

Some clarification...

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

{1,2,3,4} represents the weekday numbers that you want the count for.

Whe

1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

A1 = start date
B1 = end date

=SUM(INT((WEEKDAY(A1-{1,2,3,4},2)+B1-A1)/7))

--
Biff
Microsoft Excel MVP


"Rocetman" wrote in message
...
Trying to figure how I can take a start and end date and count the
number
of
work days based on a Monday through Thursday work schedule. Does
NetWorkdays
function have a special operative that will enable me to perform the
above
condition.




.
This helps a great deal, but is there a way to exclude the major holidays
such as Christmas, New Years, Labor Day, Thanksgiving, Memorial Day, and
4th of July. Even though this would be a simple task for one year by
setting up a string in a seperate column, but I would need to count for
several years since the job that I am on would last at least 10 years.
Have any suggestions that Excel could automatically exclude the above
holidays in "count the Number of workdays"?