• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to filter out multiple vacation ranges/dates in NETWORKDAYS holiday function by employee

orangehybrid

New Member
I have a workload spreadsheet where has one tab that shows the estimated hours per week by employee by project. I want to be able to discount the hours in a given week if a certain employee is planning to be away for a portion or all of that time.

I have another tab (let call it "Vacation1") that has a matrix of employees and vacation dates, with X's marked for the days during which each employee will be away. Vacation1 is organized with the first column containing the list of all dates in a given year (or beyond); the first row (starting in B1) showing the names of each employee; and then an x under each employee column for the dates in which that person would be away.

Alternatively, I also have another tab (let's call it "Vacation 2") that shows the same information, but in a much more compact form where the first column is for employee name, second column for the leave date, and third column for return date. Old vacation dates are not removed so that past estimates of hours are preserved, meaning that the same employee can show up more than once in the first column.

The Problem: The issue I'm having is with the NETWORKDAYS function that I'm using as part of a larger formula. I want to be able to reference a dynamic array for the "holidays" portion of the formula. This dynamic array would either look at the "x"'s in Vacation1 or the multiple ranges shown in Vacation2. Right now I have the following sub-formula that should give me the number of working days in a given week minus the number days an employee is on vacation during that work week:

=NETWORKDAYS(March 3, 2019,March 3, 2019 +5,ROW(INDIRECT(VLOOKUP("John Smith",Vacation2,2,FALSE)&":"&VLOOKUP("John Smith",Vacation2,3,FALSE))))

(Note: for the purposes of this example, please disregard regular holidays and weekends. I have accounted for those separately.)

The problem with this equation is that it only accounts for the first vacation range it finds in Vacation2 for "John Smith". But if John Smith adds any additional vacation information in Vacation2, those ranges get ignored by this function.

My failed solution: I have tried to rethink this problem by coming up with the Vacation1 matrix approach, but I am running into the issue of my own limited understanding of excel's abilities with INDEX, MATCH, INDIRECT, OFFSET, etc. I have tried the following array function (entering CSE), but keep getting #VALUE errors.:

={NETWORKDAYS(March 3, 2019,March 3, 2019+5,INDEX(Vacation1_Column1,if(JohnSmith="x",Row(JohnSmith)-Row(Index(JohnSmith,1,1))+1)))}

Where JohnSmith = the column range associated with John Smith in the Vacation 1 tab.

The idea behind this approach is to use the INDEX function to return the array of dates associated with John Smith for the dates he has marked "X" in Vacation1. This approach was roughly based on the example here: https://exceljet.net/formula/get-nth-match-with-index-match

Help me: I'm not sure if I'm applying the example from ExcelJet correctly. Please advise. Thank you!!
 
Hi @orangehybrid , welcome to the forum. Very exhaustive explanation of your issue, that's a nice change. If you could also upload your sample workbook, as explained in the forum's best practices, we can more easily assist you in finding the correct formulae. Not seeing the data often leads to ill guidance.
 
See the uploaded example. Note that there's some conditional formatting included, but those are not related to the issues I'm having.
 

Attachments

  • WorkloadPlanningExample.xlsx
    75.7 KB · Views: 18
The following relates to row 6 of sheet Workload Planning Spreadsheet.
In the array-entered formula:
Code:
=NETWORKDAYS(43402,43416,((INDEX(Vacation1!$C$4:$F$1214,,MATCH($A6,Vacation1!$C$3:$F$3,0))="x")*(Vacation1!$B$4:$B$1214)))
the 43402 is the start date Oct 29 2018, the 43416 is end date Nov 12 2018, the rest will be a long list of dates, many of which will be 0 but won't matter since that date is some time around 1/1/1900 which is out of your range of interest. I added a few xs into Brenda's (the name at A6 of the Workload Planning Spreadsheet sheet) column (F) on the Vacation1 sheeet in the pertinent date range to test.

If you have more than 4 people, obviously you'll have to extend
Vacation1!$C$3:$F$3
in:
MATCH($A6,Vacation1!$C$3:$F$3,0)
and in:
=NETWORKDAYS(43402,43416,((INDEX(Vacation1!$C$4:$F$1214,,MAT

and all the names must be distinct.

Not using OFFSET should make this formula less resource-hungry.
 
Last edited:
Back
Top