Compute the FuTure Date when Start Date,Lead Date,Weekly Offs,Leaves and Holidays..!

**e4excel** · 11-15-2008, 06:15 AM

Compute the FuTure Date when Start Date,Lead Date,Weekly Offs,Leaves and Holidays..!

Dear Daddylonglegs,

First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...

can this be possible?

**Domenic** · 11-15-2008, 01:00 PM

First, define (Insert > Name > Define) the following...

Name: Array

Refers to:

=ROW(INDIRECT(QUERY!$A$2&":"&QUERY!$A$2+QUERY!$C$2*10))

Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SMALL(IF(WEEKDAY(Array)<>6,IF(ISNA(MATCH(TEXT(Array,"ddd"),B4:B5,0)),IF(ISNA(MATCH(Array,D2:D20,0)),IF(ISNA(MATCH(Array,E2:E11,0)),Array)))),C2)

Note that the formula can be modified and made less resource intensive by adding Fri to your list in B4:B5, and by combining Holidays and Leaves into one list.

Hope this helps!

**e4excel** · 11-16-2008, 09:56 AM

Dear Dominic,

You are truly amazing!..
Its a one line formula....inducing so many different things...

But can u please explain the code and also the anomaly of one day...

HTML Code:

I would appreciate if you could explain why we are multiplying 10 to the Lead Time...

=SMALL(IF(WEEKDAY(Array)<>6,IF(ISNA(MATCH(TEXT(Array,"ddd"),B4:B5,0)),IF(ISNA(MATCH(Array,D2:D20,0)),IF(ISNA(MATCH(Array,E2:E11,0)),Array)))),C2)

I'm aware of Name defines, and think we can definitely use them in place of the coloured cell addresses...however Im getting an answer of 1 day extra so should I be subtracting 1 from your formula as a solution...

HTML Code:

Can u please explain how do I combine all the three different ranges as suggested by you?

Thanks a million^million

**e4excel** · 11-16-2008, 10:17 AM

I think I got it...

But please correct me if Im wrong...

HTML Code:

I change the number <>6 to "0" and I think it worked but i would be grateful if you could explain it step-by-step..

Does this <> 6 portion have any significance with the day of the week...?

Can I increase the number of week-offs to 3 by extending the range to B4:B6, I tried but it did not work...

**Domenic** · 11-16-2008, 11:19 AM

But can u please explain the code and also the anomaly of one day

That's because the starting day gets counted as day one. I based this solution on your previous one, which does the same. To exclude the starting day from the count, define Array as following instead...

=ROW(INDIRECT(QUERY!$A$2+1&":"&(QUERY!$A$2+1)+QUERY!$C$2*10))

I would appreciate if you could explain why we are multiplying 10 to the Lead Time...

Basically, to determine the number of dates to include in the array created by ROW(INDIRECT(....)).

Can u please explain how do I combine all the three different ranges as suggested by you?

Have a look at the attached file...

I change the number <>6 to "0"

The 6 refers to the weekday that you wish to exclude, which in this case is Friday.

**e4excel** · 11-16-2008, 11:59 AM

That was a Dominico Effffect...
Great and thanks for the explanation...

Strangely when I did this it worked..
and Sorry for the confusion created by putting the earlier code...

HTML Code:

What I meant of combining the three ranges was Dynamically from three Different sheets...

As the Leaves would be generated in the Leaves Form sheet
and the Holidays from the Holidays Sheet
and the Weekly Offs the Query Sheet..

But I think I will figure this out...

Thanks once again...

I am grateful to you for the explanation...
I wil try to explore the logic in the future..

**Domenic** · 11-16-2008, 03:55 PM

What I meant of combining the three ranges was Dynamically from three Different sheets...

To combine more than one range into a single column array, download and install the free add-in, Morefunc, and use ARRAY.JOIN. Note that the add-in can be embedded within the file itself, so there's no need for other users to install the add-in on their computers. Also, since the add-in is not compatible with my Mac version of Excel, I cannot confirm whether ARRAY.JOIN is able to join ranges from different sheets. Definitely worth a try, though.

**e4excel** · 11-16-2008, 05:48 PM

Originally Posted by Domenic

To combine more than one range into a single column array, download and install the free add-in, Morefunc, and use ARRAY.JOIN. Note that the add-in can be embedded within the file itself, so there's no need for other users to install the add-in on their computers. Also, since the add-in is not compatible with my Mac version of Excel, I cannot confirm whether ARRAY.JOIN is able to join ranges from different sheets. Definitely worth a try, though.

Dear Domenic,

I thankyou from the bottom of my heart not only for helping me with the major formula but also for extending help and support right till the end..

God bless you...you are really a Guru..

I shall definitely download the function as per your request...