+ Reply to Thread
Results 1 to 6 of 6

Payroll- overtime

  1. 04-22-2018, 05:33 AM #1
    Lukehugh
    Lukehugh is offline
    Registered User
    Join Date
    04-22-2018
    Location
    Brisbane qld
    MS-Off Ver
    2016
    Posts
    3

    Payroll- overtime

    Hi all
    Having a a little problem coming up with a formula for working out overtime for wage costing sheet
    The situation is we have a butcher shop and need to work out our wages a week in advance when doing rosters.
    What I would like to be able to do is once our team has worked over 10 hours a day they go to 1.25 time. The problem I'm having is that 1.25 time is only for 3 hours and then everything after that is 1.5 time.
    I used a if formula to work out the over 10 hour problem but then I don't know how to cap 1.25 cell to a max of 3 hours. I keep getting to many arguments.

    Can anybody help out
    Regards
    Luke
    Reply With QuoteRegister To Reply
  2. 04-22-2018, 06:07 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,098

    Re: Payroll- overtime

    You have given us no idea what your data layout is like, so i have concocted something, This formula does the job:

    =$D$1*SUMPRODUCT(--($B$7>$F$2:$F$4),--($B$7-$F$2:$F$4),$H$2:$H$4)
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 04-23-2018, 07:54 AM #3
    Lukehugh
    Lukehugh is offline
    Registered User
    Join Date
    04-22-2018
    Location
    Brisbane qld
    MS-Off Ver
    2016
    Posts
    3

    Re: Payroll- overtime

    Hi Glen
    Sorry about that i will attach the spreadsheet for you.

    G4 is the Start Time
    G5 is the Finish Time
    G6 is the LUnch Break
    G7 is the Total daily hours ( would like not to have this if possible)

    N10 is the total ordinary hours that is a max of 10
    N11 is 1.25 times is max 3 hrs
    N12 1.5 times is whatever after that.

    Hope this helps Glen

    Ridge Roster.xlsx
    Reply With QuoteRegister To Reply
  4. 04-25-2018, 08:44 AM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,588

    Re: Payroll- overtime

    Hello Lukehugh and Welcome to Excel Forum.
    To obtain the calculation in N10 without the need for G7 you could use: =IF(G5-G4-G6>10/24,10/24,G5-G4-G6)
    Similarly in N11 you could use: =IF(G5-G4-G6<=10/24,0,IF(G5-G4-G6>13/24,3/24,G5-G4-G6-10/24))
    Finally in N12 you could use: =IF(G5-G4-G6>13/24,G5-G4-G6-13/24,0)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 04-26-2018, 06:57 AM #5
    Lukehugh
    Lukehugh is offline
    Registered User
    Join Date
    04-22-2018
    Location
    Brisbane qld
    MS-Off Ver
    2016
    Posts
    3

    Re: Payroll- overtime

    Thanks so much jetemc that worked perfectly
    Reply With QuoteRegister To Reply
  6. 04-26-2018, 09:20 AM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,588

    Re: Payroll- overtime

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Payroll Template- Overtime and Overnight shifts
    By FestieQueen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2017, 02:41 AM
  2. Monthly Payroll Overtime Calculation
    By truxhavenx in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-02-2015, 04:42 PM
  3. Payroll - Deduct one amount (overtime) from one or more totals
    By ThirtyTwo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2014, 07:09 AM
  4. Payroll Formula Overtime
    By edtacc in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-26-2013, 03:31 AM
  5. lCalculating Hours, overtime, and doubletime for payroll
    By Delfino909 in forum Excel General
    Replies: 0
    Last Post: 09-07-2012, 08:05 PM
  6. Payroll: Need help with a formula that can calculate 3 shift differentials and overtime.
    By DIGITALNICO in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2012, 04:11 PM
  7. how do i make overtime in my payroll sheet calculate after 42.5 h.
    By Kaye in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-01-2005, 01:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1