WORKDAY() Function with dynamic multiple holiday ranges

[OzNjB]

I am trying to develop a formula that calculates the anticipated end date of project tasks utilizing the WORKDAY(start_date, days, [holidays]) function for use in conditional formatting based on:
1) provide start date using a named range "start_date"
2) length of projects in weeks (to the nearest 0.2 to represent part weeks e.g. 4.2 equals 4 working weeks + 1 additional day or 21 days total) using a named range "task_weeks"
3) A fixed column list of public holidays for the next 2 years using a named range called "holidays" (Holidays!$B$2:$B$39)

The basic formula for this that I was using (apparently successfully) was:
=WORKDAY(task_start,task_weeks*5,holidays)

The problem is now I want to extend the holiday date ranges dynamically by adding a column list of dates of planned annual leave taken by the assigned engineer (where known at this stage)

The formula that successfully determines the cell range containing the list of known annual leave dates for a given engineer is:

INDIRECT(HLOOKUP(eng_no,eng_holiday_range,2,FALSE))

where eng_no is a named range used in formulas to return the engineer no (e.g. for Jo Bloe: eng_no = 4)
and eng_holiday_range is a 2 row table that contains eng_no values in the first row and cell column ranges applicable to that eng_no in the second row

I was blindly hoping that I could simply combine the ranges for the public holidays & known engineer annual leave dates into the WORKDAY function as:

=WORKDAY(task_start,task_weeks*5,(holidays,INDIRECT(HLOOKUP(eng_no,eng_holiday_range,2,FALSE))))

The problem is that the function returns a #VALUE! error and so I assume that means the function can't handle a list of ranges for the [holidays] variable

Is there any way I can get this to work? Or do I have to "cheat" and simply repeat the public holiday list above each column of known individual engineer leave dates (this is doable)

[Richard Buttrey]

Hi and welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[WasWodge]

Cross-posted here

[Richard Buttrey]

@OzNjB

Subsequent to WasWodge's #3 (thanks WW) please note our rules about cross posting.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[VMG]

Hi OzNjB ,

I had a similar issue when i tried to build a dynammic holiday+leave range ;
The problem is WORKDAY function is not enhanced to take 2 list ; By default the operators are seperated by " , " (comma) and using " , " in the formula will collapse the definition of the formula ; This results in #VALUE error .
And the list separator symbol in excel is " ; " ( semi-colon )
The solution would be to merge 2 list into a single list ; I did it before 1.5 years ago ; hence i will be able to post the formula here ; But please don't ask for the definition of how it works as i did excel related formulas research and reached by goal and then i stopped.
I did it successfully by using the below code;

1. Call your "annual list of leaves of engineer" by as a new formula ' er.leaves ' = INDIRECT(HLOOKUP(eng_no,eng_holiday_range,2,FALSE)) -- i hope this is a list of leaves for specific engineer
2. Introduce a new formula 'holidays.plus.erleaves' = SMALL((er.leaves,holidays),ROW(INDIRECT("1:"&COUNT(er.leaves,holidays)))) -- This is a consolidated single list with leaves+holidays
3. In your workday function instead of " (holidays,INDIRECT(HLOOKUP(eng_no,eng_holiday_range,2,FALSE) " use " holidays.plus.erleaves " , so that the workday formula looks as
=WORKDAY(task_start,task_weeks*5,holidays.plus.erleaves)

Please do check if it works at your end.

Thanks
VMG

[FlameRetired]

@OzNjB

The formula that successfully determines the cell range containing the list of known annual leave dates for a given engineer is:

INDIRECT(HLOOKUP(eng_no,eng_holiday_range,2,FALSE))

Another way to do that:

Create a list like imagine you already have that includes holidays for 2 years and leave days. I used American holidays. Name that range ... I used Holiday_Leave

	J	K	L	M	N	O
1	Bob	John	Mike	Jacob	Kelly	Martha
2	1/1/2019	1/1/2019	1/1/2019	1/1/2019	1/1/2019	1/1/2019
3	5/27/2019	5/27/2019	5/27/2019	5/27/2019	5/27/2019	5/27/2019
4	7/4/2019	7/4/2019	7/4/2019	7/4/2019	7/4/2019	7/4/2019
5	9/2/2019	9/2/2019	9/2/2019	9/2/2019	9/2/2019	9/2/2019
6	11/28/2019	11/28/2019	11/28/2019	11/28/2019	11/28/2019	11/28/2019
7	12/25/2019	12/25/2019	12/25/2019	12/25/2019	12/25/2019	12/25/2019
8	1/1/2020	1/1/2020	1/1/2020	1/1/2020	1/1/2020	1/1/2020
9	5/25/2020	5/25/2020	5/25/2020	5/25/2020	5/25/2020	5/25/2020
10	7/4/2020	7/4/2020	7/4/2020	7/4/2020	7/4/2020	7/4/2020
11	9/7/2020	9/7/2020	9/7/2020	9/7/2020	9/7/2020	9/7/2020
12	11/26/2020	11/26/2020	11/26/2020	11/26/2020	11/26/2020	11/26/2020
13	12/25/2020	12/25/2020	12/25/2020	12/25/2020	12/25/2020	12/25/2020
14	5/6/2019	5/20/2019	5/13/2019	8/12/2019	5/28/2019	10/14/2019
15	5/7/2019	5/21/2019	5/14/2019	8/13/2019	5/29/2019	10/15/2019
16	5/8/2019	5/22/2019	5/15/2019	8/14/2019	5/30/2019	10/16/2019
17	5/9/2019	5/23/2019	5/23/2019	8/15/2019	5/31/2019	10/17/2019
18	5/10/2019	5/24/2019	5/24/2019	8/16/2019	6/3/2019	10/18/2019
19			6/24/2019			10/21/2019
20			6/25/2019			10/22/2019
21			6/26/2019			10/23/2019
22			6/27/2019			10/24/2019
23			6/28/2019			10/25/2019
24						11/29/2019
25						12/24/2019

Then with the start date in A1 this in A3 and filled down and across.

Please Login or Register  to view this content.

See the attached. Does that take care of the Holidays/Leave part?