Business Days - holidays and weekends

    I need to caluclate the number of business days using VB between two specific dates. I also need to subtract the holidays (US) and weekends. Also, if a holiday falls on a weekend and is observed on a monday, then that day could not be counted. For example:


    The number of business days between july 1 and july 5 is 2, 1)fri july 1st and 2)tues july 5th. July 2nd, 3rd, and 4th do no count because of saturday, sunday, and independence day.


    If aynone can give me a hand with this or at least get me moving in the right direction I would appreciate it. I am not real advanced with VB, but do have some knowledge. Thank you.


    ~Chris

    Re: Business Days - holidays and weekends


    have a look at the NETWORKDAYS function in XL Help - this will do what you want.


    you specify start and end dates with additional days to be excluded.

    Re: Business Days - holidays and weekends


    Howdy,


    From what you are describing the Excel function NETWORKDAYS is exactly what you are looking for... from the help file -


    "Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays"


    To use the function, you need to Add in the Analysis Tool Pak (Under "Tools/Add ins")


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Business Days - holidays and weekends


    i also need to include this in a loop that factors in many columns of data as well as holidays that are observed on mondays when the date is actually on the weekend. Basically, bank days of operations need to be counted between 2 specific dates and then return a number in a column next to it.


    ~Chris

    Re: Business Days - holidays and weekends


    with networkdays functions you specify the exception dates (not XL) so you just list those days you don't want it to use that are otherwise Mon-Fri dates.


    If this is too simplistic please post a scaled down version of your file with sample data so we can provide a VBA solution if required.

    Re: Business Days - holidays and weekends


    Here is a simplifies example and this is basically what I need to do:


    1) I need this data to be searched for certain cost centers.
    2) when it finds a cost center it needs to calculate the difference between stmnt date and meter date - i need the difference in business days
    3) then the total in column D needs to be total for each on of these instances where the differnence between the dates is 1,2,3,4,5,6,7 or 8.
    4) the total then needs to be put into a different sheet that looks similar to the sheet 2
    5) Another catch is that there is up to 25 of these 4 coulmuns X 500 rows of data
    any help would be appreciated, but it is quite complicated and tricky


    i was planning on using Loops and Ifs, adding 1 to the row value before it loops up again and then checking the next row down

    Re: Business Days - holidays and weekends


    as an aside... would anything like this be viable for you?


    unlikely if you really need the matrix as per your other sheet - this way shows how to use a pivot table instead ... do your calcs as additional fields - no requirement for VBA.


    that said I will come up with an alternative.


    do you have a list of the dates you want excluded from the workingdays calcs - ie those holidays falling midweek?


    if so can you provide us with it - XL will not be able to establish these dates itself ... it is not set up with public holiday info.

    Re: Business Days - holidays and weekends


    Well, I am not very experienced with Pivot Tables, basically I never use them, but the dates that would be omitted as holidays would be


    USA (National/NYSE) 200
    New Years Day Jan 01 17
    Luther King Day 3rd Monday in January 18
    Washingtons Birthday 3rd Monday in February 38
    Good Friday 1st Friday before Easter 26
    Memorial Day 1st Monday before end of May 19
    Independence Day Jul 04 21
    Labour Day (US/Can) 1st Monday in September 22
    Thanksgiving 4th Thursday in November 24
    Christmas Day Dec 25 25


    USA (Federal Gov) 201
    These holidays are in addition to the national holidays
    Columbus Day 2nd Monday in October 44
    Veterans Day Nov 11 23


    This is really getting quite complicated but I am trying to plug along and figure it out. Thanks for all of your help.


    ~Chris

    Re: Business Days - holidays and weekends


    ok im getting somewhere, i get the network days function to work, but only when i use the cells G4 and H4 that u used. When I change those cells to ones with the exact same dates, i get #VALUE error, any ideas.


    By the way, thanks for all your help, ive really appreciated it


    ~Chris

    Re: Business Days - holidays and weekends


    my fault...


    make the bit exceptions absolute


    so formula


    =NETWORKDAYS(G5,H5,EXCEPTIONS!$B$1:$B$11)-1


    where column G = statement date and H = metre date


    using the pivot also means you can get rid of the sumproduct formulae I illustrated before.


    however, that said - if you want to merge all the data together in the one pivot then we will need to go through that also... but one thing at a time.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login