CODEWITHSUNDEEP
excel
B.Cap
B.Cap

Reputation: 191

Simplify Networkdays Function, contains several IF options

I regularly receive spreadsheets where I need to calculate the Networkdays between 2 columns of dates.

A holiday date range also needs to be included in the function. There are 10 possible holiday ranges (named Prov1, Prov2, etc.) that I have stored in a separate spreadsheet.

The formula below selects the appropriate holiday date range per the province and is working. But I will need to expand it to cover 10 provinces so it will become a bit cumbersome.

   =IF(M18="Prov1",NETWORKDAYS(M21,N21,Holidays.xls!Prov1),_
    IF(M18="Prov2",NETWORKDAYS(M21,N21,Holidays.xls!Prov2)))

Is there a way to simplify this formula? Or a better approach?

Upvotes: 1

Views: 498

Answers (1)

user4039065
user4039065

Reputation:

If you do not mind using a volatile formula, then the INDIRECT function should suffice.

=NETWORKDAYS(M21,N21, INDIRECT("Holidays.xls!" & M18))

Upvotes: 1

Related Questions