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.

Any help would be appreciated

Thanks