Calculate Business Days in a month.

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?

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

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

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?