+ Reply to Thread
Results 1 to 11 of 11

Need Help building a schedule

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    NYUSA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    12

    Need Help building a schedule

    Hello all, I am very new to using excel and I wanted to use it to build a work schedule for my employees (over 150 people). I am finding it very difficult to calculate the difference between the time they are scheduled to come in and the time they are scheduled to leave. Employees can only work 5 days a week so on the days when they are not working I have to leave those cells open and I am receiving an error message. Not only do I need to calculate the weekly hours for each individual but also the total amount of hours we are using on a daily basis for the employees. This is where it gets complicated....I want to also account for mandatory meal breaks if the difference between the time when they are scheduled to come in and time time when they are scheduled to leave greater than 6 hours, but less than 6 they must 30 minutes needs to be deducted from the shift. If the difference between the time they come in and the time they leave is greater than 6 hours an hour must be deducted from the shift to account for the unpaid meal break. We have budget hours that are allotted on a weekly basis so I want to be able to put the weekly hours in a cell and have the scheduled hours be deducted from the budgeted hours so we I can see anytime we are either over or under budget. I would appreciate any help that anyone can offer. I have attached an example of the layout i need to use. Workbook7 (version 1).xlsx

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help building a schedule

    Would the attached work?
    Attached Files Attached Files
    Please click the * icon below if I have helped.

  3. #3
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help building a schedule

    Sorry. Error in my formulas.
    Replace ">0.33" with ">0.25"

  4. #4
    Registered User
    Join Date
    12-14-2012
    Location
    NYUSA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    12

    Re: Need Help building a schedule

    Thanks so much! The sum for the total weekly hours isn't calculated correctly? could this have something to do with the empty cells? Also only shifts that are greater than 5 hours and less than 6 hours should have 30 minutes deducted any shift less than 5 hours does not receive a paid break.
    Last edited by gburwell88; 12-14-2012 at 12:29 PM.

  5. #5
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help building a schedule

    replace w8 with the following formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    format the cell as "number" and it should work fine.

  6. #6
    Registered User
    Join Date
    12-14-2012
    Location
    NYUSA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    12

    Re: Need Help building a schedule

    I attached an example.Schedule.xlsx

  7. #7
    Registered User
    Join Date
    12-14-2012
    Location
    NYUSA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    12

    Re: Need Help building a schedule

    You are a life saver thanks so much!!

  8. #8
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help building a schedule

    Here is what I have.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help building a schedule

    To correct your other issue, replace the formula in d8 to the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Now if the hours are:
    0-5 = no break
    5-6 = 30 minute break
    6-max = 60 minute break

  10. #10
    Registered User
    Join Date
    12-14-2012
    Location
    NYUSA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    12

    Re: Need Help building a schedule

    Melvinrobb, your simply the best! I know this was probably incredibly simple for you, but you saved me tons of time and stress thanks again!!

  11. #11
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help building a schedule

    Glad to help!
    When adding and subtracting time in excel, you can always run into some odd issues. It is rarely straight forward.
    Make sure to change the thread to solved (under "Thread Tools" in the top right corner of the post).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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