If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multi NetWorkDays Calculation
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 | |
#2
|
|||
|
|||
Multi NetWorkDays Calculation
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" wrote in message ... 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 | |
#3
|
|||
|
|||
Multi NetWorkDays Calculation
Works great! Thank you.
Chris |
#4
|
|||
|
|||
Multi NetWorkDays Calculation
You're welcome.
"Chris" wrote in message ... Works great! Thank you. Chris |
Thread Tools | |
Display Modes | |
|
|