Roster 4 ON 4 OFF

Hi All,

I prepare quite a few rosters, and they vary differently practically every week, based on mining related contracts and FIFO - Fly in Fly Out transfers...

We have rosters such as a 5 4 4 5 roster - Which is some-one working 5 days ON, 4 days OFF, 4 days OFF, 5 days ON and then repeats that pattern over 12 months, or something that may operate every 4th Day only etc, meaning it never falls on the same day every week...These patterns will always repeat after a 4 week / 28 days block/period.

What I am after....is: a formula that I can drag across a date range to auto fill for 12 months based on my variables for the roster.

Eg: 4 DAYS ON / 4 DAYS OFF ( is every 4th Day )

01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan 08-Jan 09-Jan 10-Jan
ON--------------------------ON------------------------ON--------------------------ON ( PS - The lines are only so I could enter spaces )

eg: 5 4 4 5 Roster

01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan 08-Jan 09-Jan 10-Jan 11-Jan 12-Jan 13-Jan 14-Jan

ON ---------------------------------- ON ------------------------------- ON --------------------------------- ON

So refer to say cell A1 (date) as the first date and then drag my formula and it follows the pattern, by filling out eg: every 4 days with text based "ON", or on a 5445 Roster ....

Is this possible ?? I could not find any posts on something similar... any help appreciated

Sorry, I can't add attachments from work, the server is restricted so I found in my attempt, can email out though....

Cheers Eric

FYI- I use this for my standard week formula - works perefctly, I am stumped on this 4 On 4 Off autofill scenario to get it to work

=LOOKUP(INDEX(E$5:E$11,MATCH($A17,$B$5:$B$11,0)),{"N","Y"},{"",1}

By entering a Y or N range E5:E11 which is my 7 days per week Mon, Tue, We etc, and referring to the days of week, i have dates dragged down for 12 months in column B16 down, with a =text(B16,"DDD") to provide the day of week, which is my match - when it matches a Y, it places a 1 in the cell,else "Blank"

Mon N
Tue N
Wed Y
Thu Y
Fri Y
Sat N
Sun N

Hope this makes sense