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!!
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!!