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