+ Reply to Thread
Results 1 to 9 of 9

Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

  1. #1
    Registered User
    Join Date
    07-14-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    4

    Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    Hi!

    I'm working with an excel spread sheet to calculate the total hours schedule for the week within different departments of our housekeeping office. I need it to total the hours scheduled for each day and then total for the entire week. I'm running into a problem when trying to subtract a half hour lunch from a day, if the shift is 6 or more hours long.
    I've been working on this for a few days now, and haven't been able to come up with a solution. And I've searched on this forum for questions similar to mine, but none of the solutions worked in my spread sheet.

    Column D has the same formula. Column E has several different formulas I've tried, but none of them seem to work.
    excel help.xls

  2. #2
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    492

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    hi check this out

    http://www.mrexcel.com/forum/excel-q...-question.html

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,891

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    H and welcome to the forum

    Keep in mind that excel treats time as a fraction/decimal of 1 (day). So 12:00 mid day is actually 0.5, and 06:00am is 0.25.

    30 minutes is either 0.0208333333333333 or 30/60/24

    plug that into your formula and see how far you get

    Another observation - if you are just adding (or subtracting) 2 numbers, you dont need to use the SUM() function.
    =IF(C7>0,SUM(C7-B7),"0")
    can be shortened to...
    =IF(C7>0,C7-B7,0)
    Last edited by FDibbins; 07-15-2013 at 02:32 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    07-14-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    Thanks for the advice!

    I used the formula =IF(C1<=TIME(6,0,0),C1,C1-TIME(0,30,0)) from the forum you linked me to, and it works great for subtracting the half houre. Now the only problem I have is that when someone is scheduled off, I have the formula calculating "0" in column D, but it's creating a problem in column F. It just shows "#######" in column F and it messes with the totaled hours for the day. So the total for Sunday is showing as 14:30 when it should be 16.

    excel help2.xls
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-13-2013
    Location
    Great Britain
    MS-Off Ver
    Excel 2003 at home, 2007 at work
    Posts
    11

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    Try this in column E : =IF("6">D7,D7-"0.0205",D7)

  6. #6
    Registered User
    Join Date
    07-14-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    I tried it, but it still gave me no value with the rows that are "0"

  7. #7
    Registered User
    Join Date
    07-13-2013
    Location
    Great Britain
    MS-Off Ver
    Excel 2003 at home, 2007 at work
    Posts
    11

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    Ok. The trick I think is then to add a column for the days when people are off (maybe a tick box?), and then change the format from Time, to Number with 2 decimal places (ie 08:00 becomes 08.00). This allows you to calculate easily using 0.5 for a half hour, and gives you a lot of options for further use. Would this be any good? I'll knock you up a working copy if you like. Let me know.

  8. #8
    Registered User
    Join Date
    07-14-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    That would be fantastic if you could!!

  9. #9
    Registered User
    Join Date
    07-13-2013
    Location
    Great Britain
    MS-Off Ver
    Excel 2003 at home, 2007 at work
    Posts
    11

    Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

    Hi.

    Try this for size. I have left some formatting undone in-case you want to change it, although if you do use it then i'd suggest protecting the cells that have formulas in.

    I have changed the layout, and added some stuff you may not like, but worst case scenario it may give you some ideas.

    Regards,

    Digger
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-03-2015, 06:57 AM
  2. Work Schedule - Deducting Lunch Breaks
    By ManyLemons23 in forum Excel General
    Replies: 6
    Last Post: 06-11-2013, 06:45 AM
  3. Employee schedule hours tracking
    By jimothyone in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-03-2013, 02:43 AM
  4. Subtracting hours in a work schedule
    By heel4you in forum Excel General
    Replies: 45
    Last Post: 09-26-2009, 09:37 AM
  5. employee schedule
    By wingsofeagle in forum Excel General
    Replies: 1
    Last Post: 07-03-2008, 09:16 AM

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