-1

I am working on a task planner for my team and to derive end date for any task, I would need to pass the public holidays as well the team members planned holidays. Public holidays are available in a table and personal holidays in another.

While public holidays will be common for all, planned holidays will be for individuals for which i can use the filter function and identify the same.

Now, i need to provide the list of public holidays and list of planned individual holidays (filtered) to the network function.

Appreciate if you can share any ideas on how to achieve this by formulas only. No VBA.

I thought of converting the list of public holidays to an array and appending the planned holidays to that array but didn't find any formula to achieve the same.

Any help will be great.

Thanks.

Stay safe, stay healthy

Yusuf A
  • 1
  • assuming you have multiple people in the one sheet and a limited number of planned holidays (max 20???) one route you could make is a table with a column for each employee, The first rows equal to all your public holidays, and the remainder of the rows being the individuals holidays. when you use your NETWORKDAY function, use index and match to pull the appropriate list of holidays. FYI INDEX does not actually return a value, but an address so you can have formulas like INDEX(...):INDEX(...) to define a range. – Forward Ed Sep 14 '20 at 12:11
  • Have you considered putting all of your holidays into one table and simply assigning "PUBLIC" as the Individual name? – Frank Ball Sep 14 '20 at 13:21
  • Thanks for reviewing the Q. I have the below step of data in my sheet. List will keep growing. Col A, Col B, Col C, Col D Public1, 1-1-2020, Tom, 2-1-2020 Public2, 1-5-2020, Jerry, 3-5-2020 ,,Leila, 2-2-2020 ,,Aladdin,5-5-2020 and so on. So when I am scheduling a task for Tom, I want to pass all public holidays and his planned leave on 2-1-2020 so the end date is calculated correctly. I thought of using index but wasn't able to define the logic. Also referred to https://www.youtube.com/watch?v=4qZ8ud8akus which helps to an extent but unable to achieve what i want to. – Yusuf A Sep 15 '20 at 04:55

1 Answers1

0

You can concatenate these two holiday ranges using CHOOSE function. Resulting array formula will be:

=NETWORKDAYS(B2,C2,IFNA(CHOOSE({1;2},$G$2:$G$13,($J$2:$J$10)*(A2=$I$2:$I$10)),0))

or, if you use semicolon as parameter separator in formulas:

=NETWORKDAYS(B2;C2;IFNA(CHOOSE({1,2};$G$2:$G$13;($J$2:$J$10)*(A2=$I$2:$I$10));0))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

basic
  • 11,673
  • 2
  • 9
  • 26