+ Reply to Thread
Results 1 to 5 of 5

split 2 shift into 3 shift and how to auto reset series of reference along w month changes

  1. #1
    Registered User
    Join Date
    11-02-2018
    Location
    manila, Philippines
    MS-Off Ver
    ms2016
    Posts
    12

    split 2 shift into 3 shift and how to auto reset series of reference along w month changes

    I added my encoder and need to split my autofill shift from currently 2 shifts into 3 shifts but i find it hard to do.
    another thing is I am using an auto fill Reference from my table, reference pattern is 18-year, 12-month, 001-number series(1812001), how can I make number series from auto reset and back to zero when month changes
    see attached file
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: split 2 shift into 3 shift and how to auto reset series of reference along w month cha

    Please try X2 copy down

    for month reset to 1
    =TEXT(COUNTIF(W$2:W2,W2),"000")

    or include year

    =TEXT(COUNTIFS(V$2:V2,V2,W$2:W2,W2),"000")

  3. #3
    Registered User
    Join Date
    11-02-2018
    Location
    manila, Philippines
    MS-Off Ver
    ms2016
    Posts
    12

    Re: split 2 shift into 3 shift and how to auto reset series of reference along w month cha

    Thanks BO_Ry, the first formula works well, however the second one doesn't work.
    but still Im so thankful for this it really helps alot to me, maybe you have an idea on how can I split the shifting sched. from 2 shift making it to 3.
    below is the formula I used for 2 shift:
    =IF(G2="","",IF(AND(TEXT(G2,"HH:MM")>="06:00",TEXT(G2,"HH:MM")<"18:00"),"Shift 1","Shift 2"))

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: split 2 shift into 3 shift and how to auto reset series of reference along w month cha

    Please try for shift

    =IF(G2="","","Shift "&IF(MOD(G2,1)>=--"22:00",3,IF(MOD(G2,1)>=--"14:00",2,IF(MOD(G2,1)>=--"6:00",1,3))))

  5. #5
    Registered User
    Join Date
    11-02-2018
    Location
    manila, Philippines
    MS-Off Ver
    ms2016
    Posts
    12

    Re: split 2 shift into 3 shift and how to auto reset series of reference along w month cha

    thanks a lot Bo_Ry, it helps and it work.

+ 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. Replies: 4
    Last Post: 04-27-2021, 11:49 AM
  2. Shift Management Changing shifts based on last shift of current month
    By zenod in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-22-2018, 04:06 AM
  3. [SOLVED] How to reset excel for Ctrl-Shift-End to find updated last used cell?
    By london7871 in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 12-08-2017, 06:28 AM
  4. Caculating # of Day Shift Hrs & Night Shift Hrs of a Given Shift
    By DaKhoda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2014, 08:36 PM
  5. [SOLVED] If criteria met: delete cell and shift to the left but shift only over a certain range
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2013, 07:44 AM
  6. 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
  7. Replies: 9
    Last Post: 07-28-2006, 08:40 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