Count the Number of Workdays

[Rocetman]

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.

 

[T. Valko]

One way...

A1 = start date
B1 = end date

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

 

[Chip Pearson]

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

 

[T. Valko]

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.

Where:

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

 

[Rocetman]

Some clarification...


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

Where:

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

--
Biff
Microsoft Excel MVP





.
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"?

 

[T. Valko]

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))))