+ Reply to Thread
Results 1 to 2 of 2

Need formula for calculating daily labor rates including overtime.

  1. #1
    Registered User
    Join Date
    12-15-2017
    Location
    Florida
    MS-Off Ver
    Office 2016
    Posts
    1

    Need formula for calculating daily labor rates including overtime.

    Hello,

    First time post. If I am missing information, let me know and I will add.

    I have created a schedule builder for two salary and four hourly employees. I am integrating data calculations from one of my weekly reports to help speed things along.

    I want to be able to calculate the total labor rates per day for all employees working that day including the salary. I am having trouble creating the formula to calculate the daily labor rates for the hourly employees once overtime rates come into play. I have been using nested IF formulas to calculate up to 40 hours and over 40 hours. The part of the formula I am stuck on is when there are both regular and overtime hours for an employee on a single day.

    Attached is a test version of my workbook. I have blanked out unrelevant data calculations. You can see the formulas I have been developing on row 34 columns B-O. Column J is where I put the day with both regular and overtime rates. The current formula in column J is my attempt to calculate the overtime rate minus the regular rate, but I am stumped on how to add the regular hours back into the rate.

    Thank you in advance for any help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,089

    Re: Need formula for calculating daily labor rates including overtime.

    Formula is row 38 (not 34) of Sheet1 .

    Not sure what your calculation is but I would unmerge cells in B3 etc and have B3 as regular hours and C3 as OT hours: I would remove all merged cells involved in calculations

    in B3 (Regular hours)

    =MIN(8,(C2-B2)*24)

    in C3 (OT hours)

    =MAX(0,((C2-B2)*24)-8)

    in S2

    =MAX(0,P2-40)

    in R2

    =SUMPRODUCT(($B3:$O3)*(MOD(COLUMN($B3:$O3),2)=0))

    See attached Sheet3 for "Robert" (row 10)
    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. Calculating daily overtime and weekly overtime
    By Guy Montague in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2016, 04:55 PM
  2. [SOLVED] Calculating time/overtime/rates for employees - sheet attached
    By dawsonsoo in forum Excel General
    Replies: 4
    Last Post: 09-30-2014, 12:22 PM
  3. Calculating daily overtime over 8 hrs each day
    By lesha1203 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-04-2014, 05:24 PM
  4. [SOLVED] Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]
    By itsavvy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2013, 08:48 AM
  5. Replies: 3
    Last Post: 10-02-2012, 03:30 PM
  6. Calculating overtime on variable rates over the weekend
    By barrie.shardlow in forum Excel General
    Replies: 2
    Last Post: 11-03-2010, 05:41 PM
  7. Calculating overtime with 3 different pay rates
    By lostinexcelformulas in forum Excel General
    Replies: 6
    Last Post: 04-10-2009, 08:34 AM

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