Nested Ifs & NetworkDays Combined Functions

[Dawg House Inc]

OK...I've tried consulting many boards to get around the nested if limit in
Excel 2003. I remember seeing something about concatenating strings together,
but I've come to the realization that I'm being lazy.

So, here's what I'm trying to do:
I have a list of 20 people who have entered dates for their holidays.
These 20 people are from one of 8 different countries.
Each country has its own "holidays".

What I've attempted to do (and it works without concern for Holidays) is to
take subtract the number of NETWORKDAYS from the StartDate to the EndDate
(ex. =IF(ISBLANK(E12),"",(NETWORKDAYS(D12,E12,0)- 1))). This works just fine
- again, with complete disregard to holidays.

So, I created a Holidays table that consists of 8 columns. Each column
header is the country name. Under each country is a varying number of dates
(ranging from 8-17 dates). I've named each range under the country header to
match to the respective country.

This is when I thought I had it beat and I tried the following change to my
formula:
=IF(ISBLANK(E12),"",(NETWORKDAYS(D12,E12,B20)- 1))
- where B20 is the Country name for the individual.

This results in a #VALUE! error.

For more info...here's the sample sheet info:
A B C D E
1 Name Locale Start EndDate WorkingDaysGone
2 John UK 10-nov-08 12-nov-08 <formula>

For the "Country holidays" i created the following
L M
1 Canada US
2 01-Jan-08 01-Jan-08
3 21-Mar-08 21-Jan-08
4 19-May-08 26-May-08
5 01-Jul-08 04-Jul-08
6 04-Aug-08 01-Sep-08
7 01-Sep-08 13-Oct-08
8 13-Oct-08 27-Nov-08
9 11-Nov-08 25-Dec-08
10 25-Dec-08
11 26-Dec-08
Named Range: L1:L11 = "Canada"
Named Range: M1:M9 = "US"
(there are actually 8 columns in this section)

That setup...I tried to pump this formula into E2:
=IF(ISBLANK(D2),"",(NETWORKDAYS(C2,D12,B2)- 1))

I'm trying to pass the value of the Locale into the function to "act as" the
named range. It does not work.

Does anyone have any suggestions on this??

Thanks in advance.
JCH

 

[Sheeloo]

Replace B20 in your formula with INDIRECT(B20) and you should be good to go...

Test it out in a standalone NETWORKDAYS function and then plug it in your IF
statement

 

[Sheeloo]

Also make sure that you have NOT included the country name in the defined
range...

"I've named each range under the country header to
match to the respective country. " indicates that you are good but
Named Range: L1:L11 = "Canada" indicates otherwise...

 

[Dawg House Inc]

Thanks Sheloo. That indirect will be helpful going forward.

Muchly appreciated.

 

[ShaneDevenshire]

Hi,

Here is one approach:
Suppose you put your different countries in J2:Q18 with the names of the
countries in J1:Q1. Also suppose the country name for the D12, E12 data is
in F12 Then you can use the following formula:


=IF(E12="","",NETWORKDAYS(D12,E12,INDEX(J$2:Q$18,,MATCH(F12,J$1:K$1,0)))-1)


Please click yes if this was helpful.