• 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.

Networkdays: holidays from start and end dates (rather than list of each date)

K

k1s

Member
Hi

I have a series of holiday periods in a table specified with a start and end date e.g.

Code: 
Holiday      Start Date    End Date
Holiday1    22 Oct 16    30 Oct 16
Holiday2    22 Dec 16    02 Jan 17


Does anyone know of a way to use networkdays be referencing these dates that avoids having to manually create lists of all the dates between the start and end holiday dates, for each holiday period?

e.g. the results of =networkdays(30-Sep-16,15-Jan-17,holidays as above) would be 63

Many thanks in advance.
 
I made a bit of progress using indirect:

=NETWORKDAYS(Start_Date,End_Date,INDIRECT(Holiday1_Start_Date&":"&Holiday1_End_Date))

I suppose I could keep adding like this:


=NETWORKDAYS(Start_Date,End_Date,INDIRECT(Holiday1_Start_Date&":"&Holiday1_End_Date))+NETWORKDAYS(Start_Date,End_Date,INDIRECT(Holiday2_Start_Date&":"&Holiday2_End_Date))

...but that seems a bit cumbersome.

Anybody got any better ideas?
 
Not that I am aware of

You could probably make a formula to extract the list of dates from those tables
 
Hi ,

I have listed the holiday ranges in the range A2:C4. This list of holidays can be extended by adding more holiday start and end dates in row 5 and below.

Based on this data , the earliest holiday start date and the latest holiday end date have been calculated in cells E2 and F2.

Based on these two values , a named range called HolidaysList has been defined , as follows :

=MMULT(--IF(ROW(INDEX(Sheet1!$A:$A,Sheet1!$E$2):INDEX(Sheet1!$A:$A,Sheet1!$F$2))>= TRANSPOSE(Sheet1!$B$2:$B$4),IF(ROW(INDEX(Sheet1!$A:$A,Sheet1!$E$2):INDEX(Sheet1!$A:$A,Sheet1!$F$2))<= TRANSPOSE(Sheet1!$C$2:$C$4),ROW(INDEX(Sheet1!$A:$A,Sheet1!$E$2):INDEX(Sheet1!$A:$A,Sheet1!$F$2)))), {1;1;1})

The part {1;1;1} is because there are 3 entries in the list of holiday ranges ; if you add more holiday ranges , this will need to be changed.

Thereafter , I have entered your sample start and end dates in A7 and A8 , and used the formula :

=NETWORKDAYS(A7,A8,HolidaysList)

which gives the answer 63.

Narayan
 
Thanks
NARAYANK991 said:
Hi ,
I have listed the holiday ranges in the range A2:C4...
Click to expand...
Thanks, I'm finding it hard to follow. I'm seeing this in cell A2:
Code: 
=IF((StartRow-ROW())*StartRow+(StartRow+1)*(IF(RightOrLeft="R",0,1)) + (-1)^(IF(RightOrLeft="R",0,1))*((StartColumn-COLUMN())+1)<=A11Value,1,"")
 
Well thank you Narayan, you for taking time.

I've never seen MMULT before, and I certainly wouldn't have understood this as a matrix algebra problem.

I converted the table into structured references to see if I could work out what was going on in the named range formula, but I'm still not sure I understand how it works.

NARAYANK991 said:
...
The part {1;1;1} is because there are 3 entries in the list of holiday ranges ; if you add more holiday ranges , this will need to be changed.
Click to expand...

Could you help me understand what to changes these to for the scenario attached, where my Holidays are still 3 but in the Table of Dates including the non-Holiday periods?

(by the way, does the whole column $A:$A need to be referenced if the data is in a Table, or could a structured reference be used?)

Clipboard02.jpg
 

Attachments

  • Book29 (1).xlsx
    10.5 KB · Views: 6
Hi ,

In your initial post , you did not mention that the table would consist of non-holiday dates ; if this is done , then it is more difficult , if not impossible.

I have removed the non-holiday date ranges from the table.

See the attached file.

For the MMULT , the second term is {1;1;1} because there are 3 entries in the table ; if there are 7 entries in the table , this term will become {1;1;1;1;1;1;1}. We can make it appropriate for a general case , but first you need to confirm whether the table will consist of only holiday date ranges or not.

Narayan
 

Attachments

  • Book29 (1) (1).xlsx
    9.9 KB · Views: 8
Many thanks.

(by the way, does the whole column $A:$A need to be referenced if the data is in a Table, or could a structured reference to the Table column be used?)
 
Hi ,

The usage

ROW(INDEX($A:$A,val1):INDEX($A:$A,val2))

is a way of getting an array of values from val1 to val2.

In your workbook , we have a minimum (earliest) date and a maximum(latest) date ; we use this formula segment to derive an array of values from the minimum date to the maximum date.

This is in no way related to the table , and we cannot use any structured reference to derive the same output.

Narayan
 
You must log in or register to reply here.
Back
Top