+ Reply to Thread
Results 1 to 9 of 9

Rotation Pattern based on the Pattern String for scheduling tasks

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Post Rotation Pattern based on the Pattern String for scheduling tasks

    Previously I got help for the 4x4 rotation schedule based on 8 hours=1day.

    Now I'm seeking help again, Please help in this problem also.
    I've a requirement to modify 4x4 rotating schedule using a String-based pattern to define the rotation. String pattern (DDDDXXXNNNXDDDXXXNNNXXXXXXX) X=skip days/off days, D/N = Schedule days/work days.
    In this case 12 hours = 1 day.

    I'm trying to figure out how this rotation can be handled with formula. 4 days work then skip 3 off days then 3 workday then skip 1 off day then 3 work day again skip 3 days then 3 days work and then skip a whole week. and this pattern goes on.

    I've attached the workbook to show the sample data and expected outcome.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    anyone please?

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,859

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    Please try at N20
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    @Bo_Ry Thank you so much and appreciated that you give your valuable time to help me.
    The formula is perfectly working for Finish date, but for start date it is returning same result as Finish date.

    If cell value in B7 is changed from 2 to 5 then the cell value in E13 should be 8/3/19 (start date), because from B7 to B12 the sum of hours is 23 not 24. so the next task should start on 8/3/19 and finish will be 8/4/19(returned value of formula).

    like the value returned in cell C13 for Start Date. I hope i'm clear in stating the situation. please see the pic below.

    Attachment 690389

    Thank you again!

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,859

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    Maybe try at Start data at C5

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    woow! that worked as intended with little tweak, by removing -0.1 from
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    .

    stay blessed!

  7. #7
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    Sorry to bother you again @Bo_Ry, their is small error/wrong output at cell C9, if you put values in B7:B10 as 48,36,36,48. the value returned in C10 is 16-09-19 instead of 19-08-19.Attachment 690488

    this happens only with these values.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,859

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    try this at C7

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Rotation Pattern based on the Pattern String for scheduling tasks

    Thanks for helping me out! stay blessed!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Shift Rotation schedule pattern filled automatic yearly
    By Shalaby in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-14-2016, 04:24 AM
  2. Bolt Pattern Rotation
    By jimabbett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2015, 10:27 PM
  3. shift rotation pattern
    By ashintoms in forum Excel General
    Replies: 0
    Last Post: 04-22-2015, 05:06 AM
  4. Macro to insert pattern based on value and select based on pattern
    By CB569 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2015, 12:36 PM
  5. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 PM
  6. Possible feature - increment counter based on string pattern ?
    By davidku in forum Word Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2014, 04:08 PM
  7. Calcuations based on string pattern
    By goku4 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2013, 03:45 AM

Tags for this Thread

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