New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Please Help with NETWORKDAYS Different Provinces Holidays

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Please Help with NETWORKDAYS Different Provinces Holidays

Postby tces » Thu Jun 23, 2016 5:38 pm

Hi there,

I am brand new to posting here, but I use this forum regularly for answers! I am having troubles trying to find a way to integrate different holiday sets into one formula.

A1 may be "AB" "ON" or "BC" only as the user must select from a drop down list
B1 will be a manually entered date
C1 will be a manually entered date
D1 is where I want to see the number of working days based on the provincial holiday lists in another tab

I want to write something that would combine multiple IF statements for cell A1 as there is a possibility it could be either AB, ON or BC and they have different holidays which would affect the number of working days in the date range.

Code: Select all
IF(A1="AB", NETWORKDAYS(B1,C1,'AlbertaHolidays$A$1:$A$10))
BUT
Code: Select all
IF(A1="ON",NETWORKDAYS(B1,C1,'OntarioHolidays$A$1:$A$10))
BUT
Code: Select all
IF(A1="BC",NETWORKDAYS(B1,C1,'BCHolidays$A$1:$A$10))


I have tried adding them all together using the + sign between and while it does give me a result and not an error message, if i try changing A1 from "AB" to "ON" the result in D1 will not change and I know there should be a 2 holiday difference in the date range.

Any assistance would be very much appreciated, I thank you in advance for your time!
  • 0

tces
Rookie
 
Posts: 2
Joined: Jun 23, 2016
Reputation: 0
Excel Version: 2010

Re: Please Help with NETWORKDAYS Different Provinces Holidays

Postby Kevin UK » Fri Jun 24, 2016 12:26 am

Hi tces

Try the following in D1.

Code: Select all
=IF(A1="AB",NETWORKDAYS(B1,C1,AlbertaHolidays!$A$1:$A$10),IF(A1="ON",NETWORKDAYS(B1,C1,OntarioHolidays!$A$1:$A$10),NETWORKDAYS(B1,C1,BCHolidays!$A$1:$A$10)))
  • 0

Regards

Kevin
Kevin UK
Excel Master
 
Posts: 568
Joined: Jun 24, 2012
Reputation: 86
Excel Version: 2016

Re: Please Help with NETWORKDAYS Different Provinces Holidays

Postby tces » Fri Jun 24, 2016 1:01 am

Kevin UK, thank you so much!! I just tested it out in my worksheet and it works perfectly! You have no idea how much time this has saved me :D I appreciate you taking the time to help out!


Kevin UK wrote:Hi tces

Try the following in D1.

Code: Select all
=IF(A1="AB",NETWORKDAYS(B1,C1,AlbertaHolidays!$A$1:$A$10),IF(A1="ON",NETWORKDAYS(B1,C1,OntarioHolidays!$A$1:$A$10),NETWORKDAYS(B1,C1,BCHolidays!$A$1:$A$10)))
  • 0

tces
Rookie
 
Posts: 2
Joined: Jun 23, 2016
Reputation: 0
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 48 guests

cron