Networkdays macro automization help

[steelstorm]

Hi Everyone,

Please see attached worksheet:
I am looking for help with a macro that will automate myspreadsheet beyond the simple formulas I have already inserted.
First thing is I need to calculate the amount of days from “RejectDate” to “Completed Date” and insert this value into the “TAT” column. The formula I use already excludes weekendsas I would like, and I also added the -1 to the end of the formula because I donot count the first day (initial Reject Date) into the total. I just realized that I will now need to havethe following holidays excluded from the “TAT” total that I will list below.
Note: There is no set amount of orders that will be onthis worksheet, the one in the example has 70 total rejects but it can be moreor less…..depending on how lucky I amJ Definitely never more than 1000.
11-22-2012
12-25-2012
1-1-2013
5-27-2013
7-4-2013
9-2-2013
11-28-2013
12-25-2013
After all this is completed, I willneed the average days inserted in the cell directly below where “TAT”ends. In this example it is F72.
Basically all I want to have todo is run a macro to fill in the “TAT” column with the weekends and holidayslisted above excluded, then toss the average in with just a click of a button.

Thanks in advance for anyhelp/assistance.

[Paul_Hossler]

I'd just use the Holidays parameters on NETWORKDAYS and an adjustable AVERAGE function at the bottom

Really don't need VBA

Paul

[Bob Phillips]

I wouldn't subtract 1 from the result as you do because you don't want to include the start date because that start date may be in the weekend, or a holiday, so you would discount it twice.

I would use

=NETWORKDAYS(C19+1,E19,$H$2:$H$9)