Share via

Fly In Fly Out Roster formula Excel

Anonymous
2017-07-15T00:19:14+00:00

Hi there,

I'm looking for help with creating a rotating shift roster for our employees. Currently they work on a 6 days "on" 1 day "off" for three weeks then have 7 days off. This is a continuous roster. 

I have found the below formula for a different roster and started playing with it but being a beginner to excel and formulas i can't work out how to change for our shifts. can anyone help me or explain what the below formula means?

=VLOOKUP(MOD(A1-$A$1,18),{0,"ON";6,"OFF";7,"ON";8,"On";13,""},2)

Regards

Ryan

***Post moved by the moderator to the appropriate forum category.***

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2017-07-15T09:15:35+00:00

    can anyone help me or explain what the below formula means?

    =VLOOKUP(MOD(A1-$A$1,18),{0,"ON";6,"OFF";7,"ON";8,"On";13,""},2)

    Both. ;-)

    That is a combined formula from different steps, not easy to understand. If we disassemble the parts I becomes easy and simple.

    It is obvious that A1 contains a date, and A2 is the day after, so let us use this formulas in a new file:

    A1:  =TODAY()

    A2:  =A1+1

    Drag it down till row 50.

    The next step is to create the criteria, your roster has a length of 7 days * 4 weeks = 28 days:

    B1:  =MOD(A1-$A$1,28)

    Drag it down till row 50.

    As you see you'll get the numbers from 0 to 27 and then it starts again at 0. So all we have to do is to create a table for the VLOOKUP formula:

    Means we have to start with 0 (the 1st day), then *6 days "on" 1 day "off"*means the 7th day is 6, then for three weeks, means  7, 13, 14, 20. Then have 7 days offmeans our last lookup value is 21, which is the 22th day (the lookup table is 0 based, so we have to subtract 1). Sounds complicated, just write this values into the sheet:

    E1:  0

    E2:  6

    E3:  7

    E4:  13

    E5:  14

    E6:  20

    E7:  21

    F1:  a

    F2:  b

    F3:  c

    F4:  d

    F5:  e

    F6:  f

    F7:  g

    I suggest not to use "on" and "off" for this, because it is easier to understand how it works. The last step is to create the VLOOKUP formula to search for the MOD values in the table:

    C1:  =VLOOKUP(B1,$E$1:$F$7,2)

    Drag it down till row 50.

    That's it.

    In your file, replace the chars from a to g with "on" or "off" and you're done.

    I do not recommend to combine the formula as in your sample, because it has no benefits.

    Andreas.

    1 person found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2017-07-15T09:42:25+00:00

    I cant get you formula to work......must be putting it in wrong. 

    https://www.dropbox.com/s/hfu2dbjou7dxh78/1e0b48f4-d2ad-423c-b330-a65434dc08c0.xlsx?dl=0

    Andreas.

    0 comments
  3. Anonymous
    2017-07-15T09:33:47+00:00

    Hi Andreas,

    I cant get you formula to work......must be putting it in wrong. 

    Anyway i figured out the formula from earlier today and have been able to modify it to work for our rosters, but the formula is huge now!!!!

    see below 

    =VLOOKUP(MOD(D1-$D$1,28),{0,"ON";2,"RDO";3,"ON";4,"ON";9,"RDO";10,"ON";11,"ON";16,"RDO";17,"ON";18,"ON";22,"RNR";23,"RNR";28,"ON";29,"ON"},2)

    (2 days on,  1 day RDO, 6 days on, 1 rdo, 6 days on, rnr for 6 days)

    Question is now, is there a more simpler way of write this formula? if not i'm happy to keep using this as it's working

    thanks again

    0 comments