Results 1 to 3 of 3

Thread: staffing rotation in excel

  1. #1
    Seeker sv29's Avatar
    Join Date
    Feb 2017
    Posts
    6
    Articles
    0
    Excel Version
    2016

    staffing rotation in excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,680
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    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).
    If you have less than 5 posts you can add a "text" link
    Thank you Ken for this secure forum.

  3. #3
    Seeker sv29's Avatar
    Join Date
    Feb 2017
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Sorry i am new to these forums, wont happen again

    I have found a solution to my problem

    THanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •