Consulting

Results 1 to 3 of 3

Thread: Networkdays macro automization help

  1. #1

    Networkdays macro automization help

    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 amJDefinitely 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.
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I'd just use the Holidays parameters on NETWORKDAYS and an adjustable AVERAGE function at the bottom

    Really don't need VBA

    Paul
    Attached Files Attached Files

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •