# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Calculate Business Days in a month.

## Dulanic

Hello,

I am trying to calculate business days in a month. I know I can use NETWORKDAYS to remove holidays, but I don't have a end date for each month, just the start date. Each date for example is 1/1/2010, 2/1/2010, etc... I don't want to manually go in and fill in the end date for each month, so I am trying to find a different way.

For the start date of each month I just start for example with 1/1/2000 and then copy down below with that EDATE(A1,1) then then just copy down so it keeps adding a month and to save calculation time I just paste special value after to update it permanently and not have to have it recalc.

I know I have to add the holidays to the network days function.... unfortunately. Unless there is a way to fill the US federal holidays automatically.... but even if so, that would be a pain because if for example July 4th falls on a Sunday, everyone gets Monday off. I guess it may be possible to somehow calculate all federal holidays, but Im sure I'd have to pull from a external source and I think the time spent writing that would be more of a pain than doing it by hand, and I'd have to add if it fell on a sunday, to give Monday as a holiday.

Anyways, basic idea is, a way to provide me # of buisness days without manually having to enter a end date for each month using NETWORKDAYS. EDATE works half way for the end date... for example I can start with 1/31 then EDATE + 1 to 2/28 because excel knows there isnt 31 days in feb. But then to continue every day for the rest will show as 3/28, 4/28 etc.... how can I change that to the last day of that month?

----------


## daddylonglegs

You can use EOMONTH to get the last day of the month so if you just have a single reference date, 1st of the month in question, in A2 you can use

=NETWORKDAYS(A2,EOMONTH(A2,0))

add holiday range if required

----------


## Dulanic

Excellent, I did not know of the EOMONTH function. Now my other question is the holiday portion, I find that I can just list the holidays in a named range and use that for the NETWORKDAYS. Ex. =NETWORKDAYS(T5,EOMONTH(T5,0),holidays). Now my question is.... either through the named range, or I could add a column, how can I setup that if the holiday falls on a saturday, make it 0, else if it falls on a sunday, make it the next day?

----------


## daddylonglegs

There's no reason why you can't define the holiday range as two columns so if you have holiday dates listed in H2:H20 then in I2 use this formula copied down

=IF(WEEKDAY(H2)=1,H2+1,0)

Format column I as 

mm/dd/yy;;

to avoid showing zeroes as dates

now define holidays as H2:I20

----------


## Dulanic

Thanks much, I was planning on doing 2 columns to make it easy. My data starts on row 5, so for example I ended up with this:

W5 =8/1/2008
W6 =EDATE($W5,1) and this repeats down the column to last date I want.

X5 =NETWORKDAYS(W5,EOMONTH(W5,0),holidays)

Y carries the holidays I paste in for each year.

Z5=IF(WEEKDAY(Y5)=7,0,IF(WEEKDAY(Y5)=1,Y5+1,Y5))

Z is setup as named range holidays. Ex. ='$Z$5:$Z$100

Works perfect, just spelled it out so incase anyone else comes in to find how to do same thing  :Smilie:

----------


## hshayhorn

I'm trying to do the same thing. I need to determine how many actual working days are in each month this year. I also need to account for holidays. I got the formula working for workign days but I got lost a little with the holidays. Could I get a little more info on what I need to do to make that part work?

----------


## hshayhorn

Actually, I spoke to soon. When I copied the formula across the cells for the other months all the values are 22 days. That cannot be right.

----------


## daddylonglegs

Hello hshayhorn,

Please start your own thread - perhaps include the formula you tried and the difficulties that you are having - have you got "calculation" set to manual?

----------


## mikkelox

This worked out for me
=NETWORKDAYS(DATEVALUE(CONCATENATE(MONTH(NOW()),"/1/",YEAR(NOW()))),EOMONTH(DATEVALUE(CONCATENATE(MONTH(NOW()),"/1/",YEAR(NOW()))),0))

Regards
Mike :Roll Eyes (Sarcastic):

----------

