+ Reply to Thread
Results 1 to 8 of 8

Calculating Overtime Hours based on 40 hour work week

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    9

    Question Calculating Overtime Hours based on 40 hour work week

    Hi There,

    Have a question whether this is possible with a formula or I will need to use a VBA solution.

    I have two columns on a time sheet, column A is regular work hours column B is Overtime hours.

    I have trying to find a way that when the sum of column A hits 40.00 to start accruing hours in Column B

    Column A Column B
    8.21 -
    8.14 -
    8.58 -
    8.34 -
    6.73 1.52
    40.00 1.52

    Any Ideas? I can't seem to figure out anything that wouldn't be a nightmare of a formula.

    Thanks!

  2. #2
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: Calculating Overtime Hours based on 40 hour work week

    Where are the hours entered? Since a formula can only evaluate and show a result, you cannot input the hours in column A. Instead, I'd insert a column, and enter the hours in column A, and then Column B would be base issues, and C would be OT hours.
    Want to show appreciation for the help you received from a member? Give them reps by clicking the bottom left of their post!

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Calculating Overtime Hours based on 40 hour work week

    Hey Obsessed,

    Thanks for replying.

    I have attached the workbook I am working on with some sample data, you can see that the "Regular hours" already has a formula in it to derive hours from the punch times. I was hoping to modify the formula to achieve the results I was displaying above.

    PayrollTemplate.xlsx

    If you could take a look at it that would be great!

    Thanks!

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

    Re: Calculating Overtime Hours based on 40 hour work week

    Try

    in J9

    =MAX(SUM($I$9:I9)-40,0)

    in J18

    =MAX(SUM($I$18:I18)-40,0)

    Copy both down
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-13-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Calculating Overtime Hours based on 40 hour work week

    Thanks Overtime!

    That fixes one part of my problem!

    The second part would be I would want cell J15 to read 7.44 and the total "worked hours" to read 40.

    Was hoping there would be a formulaic way to "Stop" accruing time in whatever cell in the range put the total over 40 and put any excess time over to the Overtime column.

    Sorry for the clumsy explanation

  6. #6
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: Calculating Overtime Hours based on 40 hour work week

    Here you go...this should take care of you. I had to remove the "-" and replace it with "0", but you can change the cell formatting to show zeros as such. Just copy the formulas I put in Week 1 down on week 2 as well.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-13-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Calculating Overtime Hours based on 40 hour work week

    Thanks so much!

    That works perfectly, I'll have to study this as I was having a hard time putting something together that wasn't a circular reference.

    Thanks again for all of your help!!

    Marking as Resolved.

  8. #8
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: Calculating Overtime Hours based on 40 hour work week

    Basically, the logic behind the formula is "If today's hours plus previously logged hours above is greater than 40 hours, then today's hours should be 40 - sum of hours above" and then the OT formula basically evaluates "If today's hours = today's hours shown in the column, then OT = 0, otherwise, it's the difference."

+ 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. [SOLVED] Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis
    By HumdrumPanic in forum Excel General
    Replies: 5
    Last Post: 09-30-2020, 12:55 PM
  2. Calculating Work hours overnight with a 6th hour meal period
    By aryan8582 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2014, 02:05 PM
  3. Replies: 6
    Last Post: 05-16-2014, 11:30 AM
  4. Replies: 3
    Last Post: 07-21-2013, 05:45 PM
  5. Replies: 3
    Last Post: 07-11-2012, 12:37 PM
  6. Calculating monthly work hours;including 30min break;using 12 hour instead of 24
    By MichaelProcter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2011, 08:51 PM
  7. [SOLVED] Calculating a colmun to total a 40 hour work week
    By Harley mom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-20-2005, 03:45 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