+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,324

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

    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?
    Attached Files Attached Files
    Last edited by e4excel; 11-16-2008 at 12:26 PM.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    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!

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,324

    Excellent Domenic...but it gives one day more...

    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

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,324

    Please correct me If im wrong and also please explain...

    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...
    Last edited by e4excel; 11-16-2008 at 10:22 AM.

  5. #5
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,324

    Smile You are simply excellent!

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

  7. #7
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    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.

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,324

    Thanks a lot

    Quote Originally Posted by Domenic View Post
    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...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1