+ Reply to Thread
Results 1 to 16 of 16

Shift Rotation schedule pattern filled automatic yearly

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Shift Rotation schedule pattern filled automatic yearly

    HI there experts,

    I used a worksheet for managing a year shift-rotation schedule for four shifts (A, B, C and D) with 4 days working pattern through 12 hr per day for a shift (DAY and NIGHT shifts) and the other two shifts on REST and REST ON CALL. I created a formula to fill the calendar each month by matching day with date formula (i.e: 1 Jan 2017 is Sunday) then i have to fill the working shift manually in each day for the whole year with a pattern AAAADDDDBBBBCCCC.
    The question is: I need a formula or a macro to fill this automatically when i enter the year by stating for example which shift has ended the last year or any other ideas to accomplish that.

    I am trying to attach a worksheet
    Last edited by Shalaby; 11-11-2016 at 03:33 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Shift Rotation schedule pattern filled automatic yearly

    shift.jpg

    i uploaded an example file and in the "settings" sheet highlighted in yellow the needed setting to be implemened or any other ideas if easy
    Attached Files Attached Files
    Last edited by Shalaby; 11-11-2016 at 04:28 PM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    One approach:

    Yearly "Shift Calendar": 01/10/2017 to 31/12/2017 in a column

    4 other columns:

    DAY/NIGHT/REST/REST ON CALL

    Fill these columns with the shift patterns (as they are "regular" in format so copy/paste

    ****** ADD dates to Q1,Q2,Q3 charts ("Hidden" in rows 5, 15, 25) ****** not requred

    the simply do VLOOKUP or INDEX MATCH to populate your shifts.

    ******* No need to add dates as you already have them
    Last edited by JohnTopley; 11-11-2016 at 05:13 PM.

  5. #5
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Shift Rotation schedule pattern filled automatic yearly

    if i have to copy paste the pattern my self for the whole year so i wouldn't ask for help, as i already doing this every year. what i want to update is that i want just to write the year, and the file updates the shift pattern accordingly. i want to save time of copy/paste process. VLOOKUP will seek fixed data. i need it to be dynamic, at least - as i illustrated in the highlights- give the start of the pattern, for example shift B ended the previous year with its 2nd DAY shift, then it will start the new year with its 3rd DAY shift then 4th then comes the next shift C to start its 4 DAYs and so on. can i use a simple if statement to do this or even a macro

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    See the attached:

    Look at tab "Sheets"

    the tabs "Q1 (2)"

    Look at Jan 1st B8

    Using this approach you can continue year after year once it is set up.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    See the attached: VBA solution:

    Please Login or Register  to view this content.
    Tab Qx is a test sheet with CF to colour shifts.

    I have left your CF on Q1-Q3

    Run button on "Settings"
    Attached Files Attached Files

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Shift Rotation schedule pattern filled automatic yearly

    You might like the layout shown in the attachment below. I put this together some time ago for this thread:

    http://www.excelforum.com/excel-gene...t-pattern.html

    though the requirements are slightly different than yours, i.e. a 3-shift pattern, and each shift lasts one week with no weekends, but it is similar in principle. You select the year using a drop-down, then the shift on the first Friday in January, and the rest automatically populates. It could be adapted to suit your circumstances.

    Hope this helps.

    Pete
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Shift Rotation schedule pattern filled automatic yearly

    @Pete_UK
    This one is cool, but we got used to my layout. but i may try to follow the functions used ... looks pretty simple. thanks alot

  10. #10
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Shift Rotation schedule pattern filled automatic yearly

    @JohnTopley

    I'll try to follow the code to debug it coz when i change the year in the "settings" sheet it makes error. also we can use the name "year" that is assigned as a function to return the year typed in the "settings" sheet. as :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in the "shifts" sheet to update the dates in the first col.

    thanks a lot, ill be back as soon as i understand the code

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    We can add the year into a cell rather than hard code it.

    change code to ....

    Syear = Worksheets("Settings").Range("B19")

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    If you change the Year (at the moment) you will have adjust the start date row in "Shifts": currently there is no logic to determine the start based on previous year.

    So 01/01/2018 (see row 369 of "Shifts")would start in row 17 (currently 14/01/2017) and dates /shifts added accordingly: I will give some thought to "automating" the change of year (.... but not today!!!).

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    I have added some formulae to calculate B21:B25 entries in "Settings" : formulaare in Column C

    C21

    =INDEX(Shifts!$B$2:$B$400,MATCH(DATE($B$19-1,12,31),Shifts!$A$2:$A$400,0))

    c22

    =INDEX(Shifts!$C$2:$C$400,MATCH(DATE($B$19-1,12,31),Shifts!$A$2:$A$400,0))

    C23

    =INDEX(Shifts!$D$2:$d$400,MATCH(DATE($B$19-1,12,31),Shifts!$A$2:$A$400,0))


    C24

    =INDEX(Shifts!$E$2:$E$400,MATCH(DATE($B$19-1,12,31),Shifts!$A$2:$A$400,0))

    C25

    =COUNTIF(OFFSET(Shifts!$B$2,MATCH(DATE(B19-1,12,31),Shifts!$A$2:$A$400,0)-4,,4,1),$C$21)

    C26 (new)

    =MATCH($C$21,Shifts!$B$2:$B$400,0)+Settings!$C$25+1

    B19 is a dropdown list

    You will see 01/10/2018 is 4th "D" shift.

    If you change value to 2018, you should see changes in the above cells.

    Note: C26 is the row number so you will see 01/01/2018 is the 4th "D" shift which would equate to row 5 in the "Shifts" table.

    To test years after 2018, you will have to put the 2018 dates in starting row 5.

    If it works, we can add code to the VBA to reset the dates in column A of "Shifts".
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Shift Rotation schedule pattern filled automatic yearly

    well that are a plenty of brilliant formula which we wont need if we are going to fill dates till 2020 for example and copy/fill down the shifts pattern. and then we can use either the code, or for simplicity we can use VLOOKUP formula to seek fixed cells in "shifts" or index/match to search for the date

    the issue now is how to locate the specified start and end cell in the month knowing that it will change when i change the year in settings sheet.. does the code make this trick? i couldn't follow.

    i dont understand how 1/1/2018 is in row 5?

    ** I tried 2019 it returned #N/A in the new formula C21:C26 **
    Last edited by Shalaby; 11-13-2016 at 12:22 PM.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Shift Rotation schedule pattern filled automatic yearly

    If you look the unchanged file I posted, you will find 01/01/2018 in row 373 in the 4th "D" so if we are to create a shift pattern for the year 2018 we need to start in a cell which is the $th "D": hence row 5 will 01/01/2018, Row 6 will be 02/01/2018 etc.

    An alternative is just to create the shifts for required years (2020 takes forward planning to an extreme!)) in "Shifts" : I haven't tried this but the macro hopefully deals with this as it simple searches for "01/01/yyyy" and executes copy/paste for a months worth of data.

    Change code for "Syear"

    Syear = Worksheets("Settings").Range("B19")

    ** I tried 2019 it returned #N/A in the new formula C21:C26 **
    this assumes you have all 2018 data.

    The solution with multi-year data in "Shifts" is the easiest so the other calculations in "Settings" are redundant as long as you have first year pattern correct.

    Attached has shifts until end of 2020. Removed redundant calculations

    NOTE: It is necessary to clear the contents of previous year and I have limited the the CF (in Q1 only) to show the colouring for the shifts.
    Attached Files Attached Files
    Last edited by JohnTopley; 11-13-2016 at 02:22 PM.

  16. #16
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Shift Rotation schedule pattern filled automatic yearly

    I am quiet satisfied with the VLOOKUP approach, it solved my case for the timebeing with simple and smooth operation without taking time compiling with the code. Although the code could let the file works for decades but i don't want to bother.

    I have another suspended case which i will open a new discussion for it. It concerning making a dashboard for annual report of the progress of my department. I have generated all data output but what i need is to put them in a user friendly report such as infographic. I know dashboard is now available in Excel 360, I just need help how to use it, if anyone have links to useful tutorials online.

    Thanks for everything, you can consider this case SOLVED
    Last edited by Shalaby; 11-14-2016 at 04:36 AM.

+ 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. Shift Pattern Adding Hours based on Shift etc
    By fgbuk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2016, 07:56 PM
  2. Shift rotation
    By JonniBravo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2016, 01:40 PM
  3. Bolt Pattern Rotation
    By jimabbett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2015, 10:27 PM
  4. shift rotation pattern
    By ashintoms in forum Excel General
    Replies: 0
    Last Post: 04-22-2015, 05:06 AM
  5. Replies: 0
    Last Post: 10-27-2014, 11:54 PM
  6. Replies: 4
    Last Post: 02-06-2013, 04:21 AM
  7. Shift Schedule - 3 Shift Coverage - Auto Populate Roll-up Summary Schedule
    By chips1256 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-31-2012, 09:32 AM

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