Workdays in the Month formula? [SOLVED]

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello, looking for a formula that will generate a count of the workdays (Monday - Friday) without holidays for any given month of any year.



    Anyone have any suggestion on a simple way of achieving this task?



    I would also like to run this out for future months/years so flexibility would be nice.



    Thanks!

    I figured it out!!!



    I was looking to use this formula to figure actual averages across TONS of 0's/ blanks in the data set.



    I was able to use a SUMIFS formula with this formula to divide the sum of the date range by days worked.




    I was able to use the following to achieve this:


    :oops:

    Quote

    =NETWORKDAYS(AD2,AE2,Holidays)

    :oops:


    this generates my count of working days for the month, "holidays" references a list I created of Holidays scheduled closed in the office.


    Once I generated a count of days in each month I was able to vlookup this for my number to divide by.


    Full formula below:


    Quote

    =SUMIFS('Sheet1'!$H:$H,'Sheet1'!$C:$C,Sheet2!$A2,'Sheet1'!$U:$U,Sheet2!$J$1)/VLOOKUP($V$1,$AF:$AG,2,FALSE)



    Vlookup references the results from the first formula.



    Can't believe this took me the time it did... :thanx:

Participate now!

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

Register Yourself Login