+ Reply to Thread
Results 1 to 4 of 4

How to calculate hours across a specified shift..

  1. #1
    Registered User
    Join Date
    05-26-2010
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2003
    Posts
    52

    How to calculate hours across a specified shift..

    Hello,



    Ok I may have some difficulty in expressing this one, please bare with me....

    We have team leaders starting at specified times (8 hr shifts):
    I want to calculate total staff coverage over the teamleader 8 hr shift.
    there are ptime shifts of 6hrs and fultime staff 8hr shifts


    The most simple example would be:
    Team leader starts at 0700 and has
    3 f/t staff starting at 0700 = 3
    2 staff starting at 1300 = 4hrs (1300-1500 x 2 will cross team leaders 0700 - 1500 shift))
    2 pt staff starting at 1100 = 8hrs (11-1500 x 2 will cross team leaders 0700 - 1500 shift)
    so total for this day would be 4.5 staff crossing his shift. Attached is a sample of how i have set up the spread sheet minus the formulas...

    If you have a remote idea of what i am trying to express please check out and edit the spreadsheet at will :-)

    This question is more about how to calculate than anything I guess - thanks guys...

    Isabella
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-02-2010
    Location
    Grand Rapids, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: How to calculate hours across a specified shift..

    Check out the modified sheet I attached. Basicly I put a formula for each team leader next to your dropdown menu and then used vlookup to return the different formulas. You will see that the formulsa return different data when you change the number of people o a shift. I hope this is what you were looking for. I am pretty sure there is an easier way to write this but this is the best I could come up with. hope it helps.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-26-2010
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: How to calculate hours across a specified shift..

    Wow completely impressive! thank you so much - you dared to reply to someone who didnt even know how to word what they were asking and you did exactly what i was trying to achieve!

    I cannot thank you enough, I cannt tell you what a load of my mind! Thank you a million times over, you have totally made my day!

    Out of interest what where the calculations for working out the 0700/0900/1300 ref data? ie how did you know how much a part of each shift the stat times are? whats that calculation?

    Again thankyou
    xx

  4. #4
    Registered User
    Join Date
    06-02-2010
    Location
    Grand Rapids, Michigan
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: How to calculate hours across a specified shift..

    Number of hours on the shift with the team leader devided by team leader total shift hours.
    example: 0700 start time for team leader = 0700-1500
    team member starts at 1245 to 1845
    this = 2.25 hours on the shift with the team leader
    so you do this: 2.25/8 = .28125
    then you multiply the number of team members on that shift by .28125 and that gives you the shift coverage for that team leader for that specific shift.
    "If this helps please click on the scales in my post" Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1