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?
Bookmarks