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
  #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 |
  #2  
Old April 19th, 2010, 02:38 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default 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  
Old April 19th, 2010, 08:56 PM posted to microsoft.public.excel.worksheet.functions
chris
external usenet poster
 
Posts: 2,039
Default Multi NetWorkDays Calculation

Works great! Thank you.

Chris

  #4  
Old April 20th, 2010, 07:52 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Multi NetWorkDays Calculation

You're welcome.


"Chris" wrote in message
...
Works great! Thank you.

Chris


 




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:36 PM.


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