+ Reply to Thread
Results 1 to 6 of 6

Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

  1. #1
    Registered User
    Join Date
    04-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

    I'm having trouble with my Overtime Hours column (I).
    I can't figure out how to get it to show just the overtime for that day.
    I have attached the xlsx file as well as a photo of the table as is.

    The formula for that column is: =IF(ISBLANK(D20),0,(MAX(H20-40,0)))
    I want it to subtract the previous cell before it, but every time I do it returns a value error with this formula:
    =IF(ISBLANK(D20),0,(MAX(H20-40,0)-I5:I6)) Then the I6 increases

    The ultimate goal is for a spreed sheet that can calculate employee wage with a single employee that has two separate wages
    that shares the same set of 40 hours for that week in a biweekly setup with an on call bonus added at the end of the week. If I can get this overtime figured out I think I got the rest of it. I might have trouble with the job codes I'm not sure yet. I'm going to change them to numbers when I get to them, the current strings are for easy reference at this point.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

    is this the formula that you are looking for? Paste in I5 and fill down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

    No that didn't do it, thanks for the fast response though.

    Essentially I14 is correct in saying that there is only 1 hour of overtime for that day, but I18 is wrong in saying 9.25 hours of overtime was calculated when it should only say 8.25. They both use the same formula. What I have might be completely wrong.

    What i have right now is giving me the difference after 40 has been reached in column I. I don't want that. I want what would be overtime for that day in column I once 40 has been reach at some point during the week. I have to do it this way since the employee is getting different wages depending on what they do that day.
    Last edited by HumdrumPanic; 04-05-2015 at 08:32 PM.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

    How about in I5:

    =IF(ISBLANK(D5),0,MAX(H5-40,0)-SUM($I$4:I4))

    Drag down
    Quang PT

  5. #5
    Registered User
    Join Date
    04-05-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

    Awesome. That did it. Thank you both.

  6. #6
    Registered User
    Join Date
    09-30-2020
    Location
    Shreveport, LA (USA)
    MS-Off Ver
    MS 365
    Posts
    6

    Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

    Hello All,

    HumdrumPanic's original post/question is precisely my current issue with my payroll spreadsheet.

    I would like to access the link's HumdrumPanic posted in the first post but I believe I am lacking privilege's/access?

    Apologies if this is the incorrect pathway for resolving my issue.

    I appreciate and thank you for helping me with this issue.

+ 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: 6
    Last Post: 05-16-2014, 11:30 AM
  2. [SOLVED] Calculating overtime in Excel for over 40 hrs per week
    By mike4000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2013, 06:16 AM
  3. Calculating overtime hours on a daily and weekly basis
    By Skwerl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2011, 09:12 PM
  4. +40 Hours in a Week Overtime Fromula
    By Chelsea in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-28-2006, 01:55 PM
  5. Calculate Hours and overtime by week
    By gregt812 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-23-2005, 03:10 PM

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