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

Workday with non-adjacent multi Holiday ranges

S

sifar786

Member
Hi,


I am trying to find out a way to join a number of holiday ranges (defined names Rng1,Rng2,Rng3) lying in different columns into a single range and feed it to the WORKDAY function. however the WORKDAY function does not seem to work. used it as an Array, still not working.


{=WORKDAY(STARTDATE, NO_OF_DAYS, INDIRECT({"Rng1","Rng2","Rng3"}))}


i donot want to resort to VBA as its supposed to be submitted as an macro-free xlsx file.


This looks very challenging, so i would really appreciate it, if anyone with some bright ideas comes up with a definite solution on how to combine multiple holiday ranges into one single range or array of date values.
 
Hi Sifar786,


What is the challenges in putting all the list of holiday dates under one single column?


The inbuilt function workday() seems to take only one parameter of range!


Regards,

Prasad DN
 
one option I found is using array constants but not any array formula (CSE).


like this:

=WORKDAY(STARTDATE, NO_OF_DAYS, {date1;date2;date3;date4})


To convert the range of cells used for holidays into array constant, select A4:A6 (your range) in the formula, and then press F9. Repeat the same for next set of range as well. but make sure you remove the extra "{" "}". It should be like one array.


Regards

Prasad DN

PS: Picked this tip from office offline help for Workday()
 
hi Prasaddn,


to answer your question, the rng1,rng2,rng3 are holiday ranges for every country. as such they are put in different columns so that a combination of country holidays can be feeded to the WORKDAY function.


as for pressing F9 for getting the array values for the dates, i already know that. i want to achieve the same thing using a worksheet formula.
 
Here's the best I've come up with so far. Works by first rebuilding the list as a single column in 1 spot, then feeding into WORKDAY formula. To build new list:


2007+

=IFERROR(INDEX((Rng1,Rng2,Rng3),ROW(A1)-COUNT(Rng1)*(ROW(A1)>COUNT(Rng1))-COUNT(Rng2)*(ROW(A1)>COUNT(Rng1,Rng2)),1,1+(ROW(A1)>COUNT(Rng1))+(ROW(A1)>COUNT(Rng1,Rng2))),0)


2003 or older:

=IF(ISERROR(INDEX((Rng1,Rng2,Rng3),ROW(A1)-COUNT(Rng1)*(ROW(A1)>COUNT(Rng1))-COUNT(Rng2)*(ROW(A1)>COUNT(Rng1,Rng2)),1,1+(ROW(A1)>COUNT(Rng1))+(ROW(A1)>COUNT(Rng1,Rng2)))),0,INDEX((Rng1,Rng2,Rng3),ROW(A1)-COUNT(Rng1)*(ROW(A1)>COUNT(Rng1))-COUNT(Rng2)*(ROW(A1)>COUNT(Rng1,Rng2)),1,1+(ROW(A1)>COUNT(Rng1))+(ROW(A1)>COUNT(Rng1,Rng2))))


Copy formula down as far as would every be necessary, and have the WORKDAY formula reference the range of formulas you just created.


Note: I wasn't able to get the method described in this article, but it was interesting. http://www.dailydoseofexcel.com/archives/2005/01/16/union-and-intersect/
 
Last edited:
Hi sifar786 ,


I have not understood your problem fully ; did you get a wrong result when you wrote the WORKDAY formula with a composite range ?


I created 3 holiday lists as follows , in the range L1:N7 :

Code: 
List1              List2              List3
4/10/2012    4/13/2012    4/18/2012
4/17/2012    4/28/2012    4/26/2012
4/22/2012    4/29/2012    4/28/2012
4/26/2012
4/29/2012
4/30/2012

A formula such as :


=WORKDAY(B2,28,L2:N7)


where B2 contains the date April 3 , 2012 returns the correct date of May 21 , 2012 as the 28th working day after April 3 , 2012 ; this excludes the weekends ( Saturday and Sunday ) and the holiday dates defined in the range L2:N7. Where the holiday dates fall on weekends , they have been considered once , not twice.


Or does your problem arise because your holiday dates ranges are not a contiguous range ?


Narayan
 
Last edited by a moderator:
@NarayanK991


My first response to your clarifications, btw, your solutions to queries / situations are really very good and thoughtful.


Yes, you last question is correct and Sifar786 is having list of holidays in non continuous range (col).


And workday formula isn't taking more than one argument as 3rd parameter.


Regards,

Prasad DN
 
Hi Prasad ,


Thanks for clarifying.


More than one argument for the third parameter is not really a constraint ; if your holiday dates are in separated columns , say column P , column S and column W , choose them so that they are without any other data , other than dates. If your range , say P107:W123 does not contain any data other holiday dates , you can still specify this range as the third parameter ; WORKDAY will accept it and work correctly.


Narayan
 
I agree with Narayank that his porposed setup would be the easiest, but let's assume that's not possible. For instance, let's assume there's a sheet that looks like this (note, I just picked random dates)

Code: 
France    USA    India    Germany    Canada
3-Aug    4-Jul    1-Jan    16-Sep    1-Feb
5-Sep    25-Dec    2-Jan    17-Sep    2-Jul
2-Apr    24-Dec    3-Jan    18-Sep    30-Sep
And through the use of dropdowns or something I only want to use France, India, and Canada's holidays. I've not been able to find a good equivalent of VB's Union operator, and as prasaddn stated, WORKDAY doesn't like have more than one arguement in the 3rd parameter. Personally, I have to admit I like this problem because I find it challenging...I haven't given up yet, but it's certainly making me think.
 
Last edited:
Huzzah! I think I've got it figured out. One single formula, entered as an array:

=WORKDAY(H10,2,SMALL((Rng1,Rng2,Rng3),ROW(INDIRECT("1:"&COUNT(Rng1,Rng2,Rng3)))))


SMALL is able to handle the multiple arguments for the input and gives a single array as the output. =)
 
@Luke M


Hi!

I noticed something different but I didn't realize what it was. Now I see! You've gone for a walk along Rodeo Drive in Beverly Hills?

Regards!
 
Hi all,


nice work done by everyone here..


Actually we were facing the same problem while working. We use diff-diff action by dates, depends on customer request and by mistake agents used to add wrong action by date due to so many request in a day. so I created a worksheet where you just need to enter the date & you will get all the action by dates from 1 business day to 15 business days (excluding Sat-Sun & client holidays).


*sifar786's- Your problem looks like almost same but you have 2-3 lists of holidays. You can try this sheet as it has editable year, month and dates for quality check (to check previous dates).


*Luke M- GREAT WORK! SUPERB!! I tried by using your formula but it includes holiday as well in my worksheet. Please help me in this.


here is the link-

https://skydrive.live.com/redir.asp...92BD629A339D0180!112&authkey=!ADhx_0FZqyMbE3M


This sheet is protected and only one cell is editable. so that agent can enter only date easily without harming any formula. please remove protection and use it.


Thank you.


Atul
 
Last edited by a moderator:
Hi Atul Rajratna,


the formula posted by Luke M is an array formula. as such you need to press F2 and then Ctrl-Shift-Enter for the dates to appear.


Cheers! :)
 
You must log in or register to reply here.
Back
Top