networkdays function

[Anonymous]

my formula stands like this =sum(sales)*std-value/(no of working days in a month)

Parameters:

no of working days= Monday to Friday (sat,sun and public holidays excluded)

number of holidays/year = 12

Std-value=any arbitrary number(lets say 2.5 for example).

Using the networkdays function i have calculated the number of working days for the etire year as 249(minus holidays).

Then i tried to filter it  month wise(jan, Feb...) using the list box, But the value does not change and

remains at 249!

Please help me to fix this.

note: I am able to plot a standalone chart for sum(sales)/month and i can change it month-wise using a list box.

I have attached the excel sheet.

Holiday list.

    holiday 1, 26-01-2015

    holiday 2, 17-02-2015

    holiday 3, 14-04-2015

    holiday 4, 05-01-2015

    holiday 5, 07-17-2015

    holiday 6, 09-17-2015

    holiday 7, 10-02-2015

   holiday 8, 10-23-2015

    holiday 9,11-12-2015

   holiday 10, 25-12-2015

    holiday 11,28-12-2015

    holiday 12, 29-12-2015

Any help would be nice

[swuehl · Accepted solution]

You've probably hard coded year start and end into your networkdays() function call.

Try using a dynamic date range, maybe like

=networkdays( min(DateField), max(DateField), $(vListOfHolidays) )

[Anonymous]

Thank you swuehl.. it worked

[sunny_talwar]

I suggest marking swuehl‌'s answer as correct to close the thread.

Best,

Sunny

[David_K]

Hi Swuehl,  Can you explain what is meant by $(vListOfHolidays) ) . I have a table for holidays, but do not know how to link it together with my main dates