+ Reply to Thread
Results 1 to 2 of 2

Employee roster with 30 0r 60 minutes break deduction based on hours worked

  1. #1
    Registered User
    Join Date
    05-19-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    21

    Employee roster with 30 0r 60 minutes break deduction based on hours worked

    Please see attached an excel sheet with formulas done as below though I am struggling to get this completely correct when the start and end times are entered in half past times
    Calculations/formulas as below
    If worked from 6 hours to 11 hours per day = Deduct 30 minutes’ break time
    If worked from 6 hours to 11 hours per day = Deduct 60 minutes break time

    Employee 1 – all fine with the total hours on shift, relevant break time deducted etc.
    However, when the rota time is entered as half past the total hours becomes incorrect. Please see reference “Employee 2”. Rota Hrs (AG10) shows “8.3” instead of “8.5” and Pay Hrs (AH10) shows “7.3” instead of “7.5”
    Employee 3- Rota Hrs(AG13) shows “15.6” instead of “16” and Pay Hrs (AH13) shows “14.6” instead of “15” as I expected.

    Could you please help me with this?

    Notes
    • As the rotas are done by others I have formatted the IN and OUT time rows are formatted as numbers to avoid semicolon. However, inserting “.5” for half past would confuse the staff so inserting as “.3” (Example cell P10- inserted as 14:30 instead of 14.50)
    • Text’s – “H”,”BH” and “LD” are calculated as 7.5 hours and the total appears in Extra Hrs Column (AI) and then adds to total pay Hrs (AJ)
    • Texts “E” and “L” are calculated as “8” hour shift and calculates the same with deducting 0.5 hours

  2. #2
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    Office 365
    Posts
    99

    Re: Employee roster with 30 0r 60 minutes break deduction based on hours worked

    Hi
    the trouble you are having is you have put in 14.30, this may be 2:30pm to us but Excel sees it as 14.3. If you put 14.5 your formula works.
    thanks
    Leigh

+ 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. Adding Hours Worked on Employee Schedule based on varying daily criteria
    By CGF2704 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2016, 11:19 AM
  2. Formula tp ca;culate hours worked using military hours and standard minutes
    By Vicki Steffen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2014, 03:03 PM
  3. [SOLVED] Need formula to auto-deduct 30 minutes from hours worked if over 6 hours
    By BluesJunkie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-06-2014, 03:07 PM
  4. [SOLVED] Adding Hours Worked minus lunch break IF over 5 hours
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2013, 11:16 PM
  5. [SOLVED] Calculate break based on hours worked.
    By RAS 2112 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2013, 12:57 PM
  6. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  7. Replies: 2
    Last Post: 02-11-2013, 02:26 PM

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