4 on 4 off schedule

[Sky188]

I'm wanting to build a scheduling tool in Excel for my employees that work 4 days on, 4 days off. I have my year, month, and employees in column A. In row 1, I have the # of days of the month (For example, 1 - 31 for January). In row 2 I have the days of the week that corresponds to the number in row 1.

My ultimate goal is to find some formulas that will allow me to copy and paste rows 1 - X (where X is the row detailing the last employee), change the month, and have those pasted formulas look at the prior month, see where the prior month's rotation ended, and then start it again.

For example, if I have January with a single employee that worked 1/6 - 1/9, 1/14-1/17, 1/22-1/25, and 1/30 - 1/31, I want to be able use a formula that will see that the last two days of the prior month were worked, so the first two days of February should be worked.

I know I can do an insanely long formula saying "If 31, 30, 29, and 28 = Worked, pull 'off', if 31, 30, 29 = Worked but 28 = "off", pull "work", etc. On top of this, I'm running into the complication of referencing the last day of each month. Per my prior formula example, I would need to add in a "if the 31st exists, pull all this criteria, if not, see if the 30th exists and if so, pull all this criteria, if not, see if the 29th exists and if so, pull all this criteria, and if not, see if the 28th exists and if so, pull all this criteria".


Hopefully that makes sense, and I appreciate your help!

 

[jmacleary]

Hi there, welcome to the forum. Without seeing the structure of your worksheet, this may be wrong, but if you add a column next to each employee showing their first 'on' day (any on day from the past would do but you might as well put in the date they actually started the shift pattern, and at the head of that column (or anywhere else thats convenient) put the date of the first of the month that you want to display, then an automatically calculating sheet can be set up with the following formula:

Assuming the date of the first of the month is in B2, and each employees first on day is in column B as well, and the first employee in in row 3 and 1 to 31 runs across row A:
place the following formula in C3: =IF(MOD(($B$2-$B3)+C$1-1,8)<4,"ON","OFF") and copy across for 31 days and down for each employee.

The only change you will need from month to month is to change the value in B2 to the first of the next month - all other stuff will recalculate from that.

What it does is calculate a modulus from 0 to 7 by subtracting the first on day from the date of the month and assign 0 to 3 as ON days,4 to 7 as OFF days. This means you never need to adjust anything for variable month lengths etc.

Hope this helps.
John

 

[NayLikesItGood]

I can see this is a very old post but maybe still around to help.

Is there a way to edit this formula to include a day and night schedule? So 4 days then 4 off then 4 nights then 4 off?