staffing rotation in excel

[sv29]

I am currently working on a construction project that has multiple different rotations schedules (some are on a 14 day on, 14 days, other 9 days on 5 days, others are 5 days on 2 days off, etc). In order to do a detailed forecast per month going forward I need to map out everyone's work hours per month, which is a very lengthily process to do manually when everyone is working different hours, rotations and have different start and and end dates. Is there a way or a formula which would take into account an individuals rotation schedule, hours worked per days, contract start and end date, and produce the amount of hours an individual will work in each month?

Ex.
Adam, works 13 hour days, works 9 days on and 5 days, off, starts work on Feb 15 2017, and is finished Sept 30 2017

If i map it out manually I can figure out Adam will work 234hrs in Feb, 273hrs in March, 260 in April, etc. etc.

Since rotation, hours per shift, and schedule start and end dates are constantly changing, I want to have a formula that will take these variables into account and produce the hours per month rather then mapping out 200 people manually everytime there is changes.Please see attached sample spread sheet. Tab 2 is the detailed breakdown (this is how i am currently doing it) based off the individuals start date and rotation i go in an manually figure out which days the person will be working and adding the hours to that day, from there i can simply figure out how many hours they work in a week. The issue with this is there is about 200 employees whose rotation, hours worked, and start or end dates constantly adjust/change, so it becomes alot of work every month. If there is a formula that could do the calculations for me (based of the changes in make to rotation, hours, or start and end dates) it would simplify my life alot. At the end of the day i want to just go in and change (say Adam for example) work days to 12 and non-work days to 9 and his end date to 10-11-16 and have the hours worked detailed table update itself, rather then having to go in and manually update everything.

I have also included a second tab called summary; if there is a formula to figure out the hours work just as a summary per month, that would also work.

Any help would be appreciated

[p45cal]

Try in cell I3 of your file's Summary sheet:

=SUMPRODUCT((ROW(INDIRECT(I$2 & ":" & J$2-1))>=$F3)*(ROW(INDIRECT(I$2 & ":" & J$2-1))<$G3)*(MOD(ROW(INDIRECT(I$2 & ":" & J$2-1))-$F3,$B3)<$C3))*$E3

copy down and across.

In K2 of your file's Detailed sheet:

=AND(MOD(K$1-$F2,$B2)<$C2,K$1>=$F2,K$1<=$G2)*$E2

copied down and across.

[p45cal]

…oh groan, a cross poster without supplying links.
Read the blinking rules!!

[sv29]

Thank you very much p45cal, this works perfect for what I need.

PS. sorry about the cross site, I am new to these forums