+ Reply to Thread
Results 1 to 9 of 9

Adjust the plan date if busy

  1. #1
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Adjust the plan date if busy

    Hi everybody,
    In my local excel forum has a query:
    They do planning for the task, for instance, Task 1, plan date: 15-Mar. Now, they review and findout that they are busy that day, and may be some next days.
    How to assign the new plan date to closest date that is not busy.
    See attachment.
    I've built my solution already, but it so complicated and i am not sure it is the best!
    Attached Files Attached Files
    Quang PT

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adjust the plan date if busy

    Try this array formula

    =MIN(IF(ISNA(MATCH(ROW(INDIRECT(C4&":"&C4+100)),I3:I10,0)),ROW(INDIRECT(C4&":"&C4+100))))

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Adjust the plan date if busy

    I see a potential issue with "non-busy" days becoming busy days. For example, plan date for rows 4 and 5 (17th and 18th) are both moved to the 19th. Everything would need to be moved out. The formula would not only need to look at column I but also at previous results in col D. Hmmmmmm
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Adjust the plan date if busy

    Thanks Bob, it works well!
    I think this is the best so far, in comparison with mine!
    Simple that create a range of date and match with "busy date", we here almost quit
    @ChemistB: My false, 19-Mar should be "does not change", instead of "change"

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Adjust the plan date if busy

    Hi bebo,
    My point was, let's say we had plan dates for 16,17, 18 and 19. As the 16,17 and 18th are listed as busy days, everything is moved to begin on the 19th. When is the 19th thought of as a busy day? We are now starting 4 new projects on the 19th. How many projects can one start on the same day?

    ChemistB < too analytical

  6. #6
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adjust the plan date if busy

    That is easily handled by extending the formula

    =MIN(IF((ISNA(MATCH(ROW(INDIRECT($C$4&":"&$C$4+100)),$I$3:$I$10,0)))
    *(ISNA(MATCH(ROW(INDIRECT($C$4&":"&$C$4+100)),$F$2:$F2,0))),ROW(INDIRECT($C$4&":"&$C$4+100))))

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Adjust the plan date if busy

    What goes in the F2 list?

    Edit: Nevermind. Looks like the D2 List, yes? Very nice Bob.
    Last edited by ChemistB; 03-13-2012 at 11:50 AM.

  8. #8
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adjust the plan date if busy

    Oh yeah,I forgot I put it in another column

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Adjust the plan date if busy

    I 've found another formula, same result:
    =INDEX(D3+ROW($1:$100)-1,MATCH(1,--ISERROR(MATCH(D3+ROW($1:$100)-1,$I$3:$I$10,0)),0))

+ 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