Networkdays Function

  • Re: Networkdays Function


    Rob,


    This should do it.


    NB: dates are inclusive, change function name to something less wordy that will be relevant to you.


    Regards
    Weasel


  • Re: Networkdays Function


    Hi Weasel


    Thanks for that, the problem I now have is recreating the Holidays that NETWORKDAYS allows for.


    Any suggestions?


    Thanks for your help
    Rob

  • Re: Networkdays Function


    Rob,


    Do you mean that you wish to remove 'Bank Holidays' from the calculations?


    If so, this is not so easily done as these days change year by year, country by country. You will probably need to keep a list of them somewhere in a worksheet, however this has disadvantages:


    1) If you ammend the dates each year any instances of the function that refer to previous years will not be accurate.
    2) If you continually add each years holidays to the list the function will be exponentially slower as the the number of usages & the number of holidays both increase.
    3) This function will only operate in worksheets that contain the list.


    Can you provide a little more information as to the scope of the spreadsheet and we may be able to advise if such a case is worth the effort.


    Regards
    Weasel

  • Re: Networkdays Function


    Hi Weasel


    I have created an attendance record, 1 sheet per month, which will be completed on a daily basis by entering a 1 for attendance or a selection of single alpha codes indicating Holiday, Sickness, Training absences. The networkdays function works perfectly in calculating the total number of available working days for each month, taking into account Bank and Statutory holidays which I have stored in the workbook as a range named "Banks".


    The problem arises because there are those employees who work 1 Saturday in 4, so I thought that amending the current function to include all Saturdays and then assuming that 3 Saturdays of 4 would be considered "holiday" and creating 4 new holiday lists (1 for each shift pattern) would solve the difficulty.


    I came up with this



    Where the Range is the list of days in current month, and Holidays is the named list of Banks & Stats


    which, naturally, does not work, but I'm blowed if I can see why.


    My apologies if this is not as clear as I think it is, your comments would be much appreciated.


    Regards


    Rob

  • Re: Networkdays Function


    Rob,


    Without actually setting up a test bed, I do see one immediate comcern:


    1) Not all months have 31 days. (Do Until n = 31)
    2) Days that have 30/31 days can include 5 Saturdays (eg. this month, April, July....)
    3) If you are looking at 1 Saturday in 4 maybe you should be basing your timesheets around 28 day cycles rather than calander months.


    Regards
    Weasel

  • Re: Networkdays Function


    Hi Weasel


    Thanks for your comments. The value of n refers to the range D2:AH2, where the value exceeds the days in the current month the target cell/s will be blank so will not increment the count. The problem of having 4 or 5 Sats in a month I had hoped to circumvent by including non-working saturdays in the list of Holidays. Unfortunately, the 28 day cycle idea is not feasible in the current requirement, worse, I now learn that working saturdays are a matter of negotiation on the day before, and, even then, honoured more in the breach than the observance.


    So, I have come to the conclusion that I cannot cater for all these variables and so will have to accept that there is a certain amount of post-event adjustment.


    I would still like to know what it is about the code I tried to use, particularly the COUNTIF statement.


    thanks again for all your input and interest


    Regards
    Rob

  • Re: Networkdays Function


    Some things in life are like your love life after you have kids.....
    Sometimes it's quicker and easier to do it by hand :)


    Regards
    Weasel

Participate now!

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