Counting holidays

[mfleuette]

I searched this forum but was unable to find relevant threads, so I am posting here in the hope that someone out there can help with this problem.

I have an application that requires me to take holiday schedules into account when calculating NETWORKDAYS between a startdate and an enddate

If I had only one holiday schedule to work with, I could use NETWORKDAYS with a named range for the HolidayList

However, I have multiple global locations to consider (e.g. New York, Paris)

Q1: How would one construct a holiday range for NETWORKDAYS such that the location could be taken into effect (i.e. create two named ranges (New_York_Holidays, Paris_Holidays) and parse into cell formula?
pseudo_code = NETWORKDAYS(startdate, enddate, locationName&"_Holidays")

If unable to use method proposed in Q1, then a function would need to be written to count the number of rows in the HolidayList range that fell within a given date range for a given location. The holiday list currently looks like:

Year Holiday Date New York Paris
2008 New Years Day 1/1/2008 X X
2008 Independence Day 7/4/2008 X
2008 Bastille Day 7/14/2008 X
2008 Christmas 12/25/2008 X X

The function would return 2 for (1/1/2008,7/10/2008, New_York_Holidays) and
1 for (1/1/2008, 7/10/2008, Paris_Holidays)

Q2: Is there a better way to structure the holiday data that would make it simpler to count rows?

Q3: If the above sample data were a named range (e.g. HolidaysList) how would it be referenced in a function CountHolidays(start, end, location) in order to count the relevant rows?

Any and all help on this issue is greatly appreciated -

Mike

 

[Jonmo1]

Using your Psudocode, for the named ranges method, you need indirect

=NETWORKDAYS(startdate, enddate, INDIRECT(A1&"_Holidays"))

Where A1 is a designated cell to put the location in (New_York_,Paris_,etc)

Hope that helps...