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.



Any help would be appreciated

Thanks

[Pecoflyer]

Please post a sample sheet containing some data , showing what goes wrong and expected results. ( click - Go advanced - Manage attachments)

[sv29]

Nothing is really going wrong with what i am doing because i literally have to enter all the data manually, i am hoping someone can suggest a formula which will calculate everything automatically.

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.

Thanks

[Pecoflyer]

Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[sv29]

Thanks to anyone who was looking into this issue for me, I have now found a solution

[Pecoflyer]

Please post your links to cross post as requested in post #4. Thx

[sv29]

There were a few different solutions, please see links below;

http://www.vbaexpress.com/forum/show...957#post355957

http://forum.chandoo.org/threads/sta...n-excel.32978/