Joining two list to form a single list

[cvempati]

I have scenario where I need to calculate resource availability for my project in terms of business day. I am using Networkdays function. I have one list (created by define name of excel) which has list of holidays and another set of lists for leaves/vacations for each resources. Now to know each resource business days availability I need to combined this two list i.e. one holiday list and another list which has leaves/vacations for the individual resource for example if I have 1 month project starting from July 1<SUP>st</SUP> 2009 and ends on July 31. Now my holiday list has holiday on 07/03 and I have two resources working on this project one resource (say Tim) has taken the vacation on July 6 and another(say Tom) took July 6 and 7<SUP>th</SUP>. That means Tim is available 21 days and Tom is available 20 days. How to achieve this without repeating the list of holidays in the individual list. I don’t want to use VBA and want to use excel function.

Thanks in advance.

 

[barry houdini]

One way to do this is as follows:

If you have a header row H1:Z1, with "Holidays" in H1 and then employee names in I1:Z1, e.g. Tim, Tom etc. Under each you have a list of holidays, i.e. all general holidays under "holidays" and specific leave under each name.

Then with start date in A2, end date in B2 and employee name in C2 you can use this formula:

=NETWORKDAYS(A2,B2,IF((H$1:Z$1=H$1)+(H$1:Z$1=C2),H$2:Z$20,0))

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

 

[cvempati]

Thanks Barry. It worked.