A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multi NetWorkDays Calculation



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old April 18th, 2010, 02:53 PM posted to microsoft.public.excel.worksheet.functions
chris
external usenet poster
 
Posts: 2,039
Default 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 |
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.