Multi NetWorkDays Calculation

[Chris]

I'm attempting to create a staff availability table by month. Each row is a
person and each column is a month.

Person | Apr-10 | May-10 | Jun-10 |
John Doe | | | |
Mary Doe | | | |

There are two additional tables: NetWorkDaysTable and OutofOfficeTable.

NetWorkdaysTable includes:
Month | Start | End | Days
Days is =NETWORKDAYS([@Start],[@End])

Example:
Month | Start | End | Days
Apr-10 | 4/1/10 | 4/30/10 | 22
May-10 | 5/1/10 | 5/31/10 | 21

This is the basic net work days in a month.

OutofOfficeTable includes:
Person | Event | Start Date | End Date | Days
Days is =NETWORKDAYS([@Start],[@End])

Example:
Person | Event | Start Date | End Date | Days
John Doe | Parental Leave | 10-Apr | 23-Apr | 5
Mary Doe | Vacation | 26-Apr | 26-Apr | 1
John Doe | Sabbatical | 6-Jul | 16-Aug | 30


For the main table (StaffAvailability), I need to get the total net workdays
for each person per month. I'm aware of the [Holidays] optional argument for
the NETWORKDAYS function, but not clear on how in the main table to get
filter each cell's calculation based on the Person in that row. In other
words, B2 needs to subtract John Doe's holidays from the OutofOfficeTable
table for days within April from the available work days for April found in
the NetWorkDaysTable table.

Person | Apr-10 | May-10 | Jun-10 |
John Doe | B2 | C2 | D2 |
Mary Doe | B3 | C3 | D3 |

 

[Steve Dunn]

Something like this:

=VLOOKUP(DATE(YEAR(StaffAvailability[#Headers]),
MONTH(StaffAvailability[#Headers]),1),
NetWorkDaysTable,4,0)-SUMPRODUCT(OutofOfficeTable[Days]*
(OutofOfficeTable[Person]=[@Person])*
(MONTH(OutofOfficeTable[End
Date])=MONTH(INDEX(StaffAvailability[#Headers],COLUMN()))))

 

[Chris]

Works great! Thank you.

Chris

 

[Steve Dunn]

You're welcome.