+ Reply to Thread
Results 1 to 2 of 2

Need formula for calculating daily labor rates including overtime.

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Office 2016

    Need formula for calculating daily labor rates including overtime.


    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
    MS-Off Ver
    Office 2010

    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)


    in C3 (OT hours)


    in S2


    in R2


    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


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