How do I count workdays using an excel formula?

  • Thread starter Thread starter Art MacNeil
  • Start date Start date
A

Art MacNeil

Hello,

I have a spreadsheet where I manually count the number of workdays in a
given month, then I multiply those days by 8 hours. This tells me how many
hours I've worked that month over or under the expected amount.

For the last few years I've manually counted the number of work days for
each month. Is there a formula that will determine the number of work days
in any given month?

My spreadsheet does have the month in Row A. For example Row 2, Column BW
is October 2006. Is there a formula that will point to Row 2, Column BW and
recognise it as October 2006 and determine there is 21 work days?



Thank you for your help,

Art.
 
What are the workdays, Monday through Friday? Do you want to exclude any
holidays?
For example Row 2, Column BW is October 2006.
Click to expand...

Is BW2 a formatted date to appear as October 2006 or is it a TEXT entry?

Take a look at the NETWORKDAYS function in Help.

Biff
 
Hi Art,

As Biff suggested look at NETWORKDAYS(). In this example it will return all
weekdays in a particular month, where A2 contains the date e.g. 01/12/2006.
In addition it will eliminate holidays, e.g. A3 contains 04/12/06 so it will
return 20 days as a result:
=NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),EOMONTH(A2,0),A3)

Regards,
Jon
 
Thank you for replying Biff and Jon.

What are the workdays, Monday through Friday? Do you want to exclude any
holidays?
Click to expand...
For example Row 2, Column BW is October 2006.
Click to expand...
Is BW2 a formatted date to appear as October 2006 or is it a TEXT entry?
Click to expand...

Monday through Friday with holidays excluded is what I'm looking for.

BW2 is formatted as "mmm. yyyy"




I entered 01/12/2006 in A2 and 04/12/06 in A3 and used the formula below but
I get a #NAME? error.

=NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),EOMONTH(A2,0),A3)



Art.
 
I have Excel 2003 and it's up to date but I don't have this function but I
don't have EOMONTH or NEWORKDAYS. Can the formula be rewritten so it
doesn't use either of these. Office 2003 came with my computer so I don't
have a CD to install the Analysis Tool Pak add-in.
 
Tools>Add-ins>Analysis ToolPak must be checked for this function.


Gord Dibben MS Excel MVP
 
I entered 01/12/2006 in A2 and 04/12/06 in A3 and used the formula below but
I get a #NAME? error.

=NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),EOMONTH(A2,0),A3)
Click to expand...

It's always a good idea to check HELP for functions. Seems to describe your
exact problem:



If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

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
 
Hi Art

Without using the ATP functions.
In place of Networkdays

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))


Where A1 holds your start date and B1 your end date
This uses WEEKDAY(date,2) to make Monday =1 and Sunday =7, so Weekdays
<6 will be working days.
ROW(INDIRECT(A1&":"&B1)) creates an array of all of the dates between A1
and A2, and SUMPRODUCT adds all the values that Match the criterion of
being less than 6.

This is an adaptation of a formula that I first saw posted by Biff.

In place of EOMONTH
=DATE(Year(A1),Month(A1)+1,0)
relies on the fact the 0th day of a month is the same as the last day of
the previous month
and will return the same value as =EOMONTH(A1,0)
Change the addition of +1 after Month to compute other months
 
That seems to do the trick, thank you.


Roger Govier said:
Hi Art

Without using the ATP functions.
In place of Networkdays

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))


Where A1 holds your start date and B1 your end date
This uses WEEKDAY(date,2) to make Monday =1 and Sunday =7, so Weekdays <6
will be working days.
ROW(INDIRECT(A1&":"&B1)) creates an array of all of the dates between A1
and A2, and SUMPRODUCT adds all the values that Match the criterion of
being less than 6.

This is an adaptation of a formula that I first saw posted by Biff.

In place of EOMONTH
=DATE(Year(A1),Month(A1)+1,0)
relies on the fact the 0th day of a month is the same as the last day of
the previous month
and will return the same value as =EOMONTH(A1,0)
Change the addition of +1 after Month to compute other months
Click to expand...
 
To exclude holidays:

J1:J10 = list of holiday dates to be excluded from the calculation

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),J1:J10,0))))

There's a slightly shorter alternative formula to do this but I don't
understand the logic behind it and no one has been able to explain it to me
so I prefer this formula.

Biff
 
Very nice Biff!!

--
Regards

Roger Govier


Biff said:
To exclude holidays:

J1:J10 = list of holiday dates to be excluded from the calculation

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),J1:J10,0))))

There's a slightly shorter alternative formula to do this but I don't
understand the logic behind it and no one has been able to explain it
to me so I prefer this formula.

Biff
Click to expand...
 
Thanks to "Gord Dibben MS Excel MVP".

Your tip helped me solve the issue in 2003 :): Tools>Add-ins>Analysis ToolPak must be checked for this function.

Cheers, Pramod
 
Back
Top