+ Reply to Thread
Results 1 to 17 of 17

Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

  1. 10-18-2016, 11:55 AM #1
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    I am currently working on a sheet for our payroll lady that will split total hours into reg time and ot once you put in the total hours worked in the day. For this I am using the formula =IF(C8>8,$AF$7,C8)
    and then for the ot for the day I am using this formula =IF(C8>8,C8-$AF$7,"")

    The issue I am having is not with the totaling of the hours at the end of the week but I want the column with the regular hours to max at 44 once the employee reaches 44 reg hours in a week.

    Does this make sense?

    Day 1- 12 hours = 8 reg 4 ot
    Day 2- 12 hours = 8 reg 4 ot
    Day 3- 12 hours = 8 reg 4 ot
    Day 4- 12 hours = 8 reg 4 ot
    Day 5- 12 hours = 8 reg 4 ot
    Day 6- 12 hours = 4 reg 8 ot
    Day 7- 12 hours = 0 reg 12 ot

    I'm having problems making the "day 6 and day 7" columns adding the over 44 hours into the calculation.

    Sorry again, just first post so hopefully this makes sense.
    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-18-2016, 12:07 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    Welcome to the forum!

    Please provide a copy of the attachment populated with some dummy data for us to work with that shows your expected outcomes.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-18-2016, 12:12 PM #3
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    I'll try. Not sure how just yet. Sorry I'm new here ha ha
    Reply With QuoteRegister To Reply
  4. 10-18-2016, 12:13 PM #4
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    There hopefully this worked...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-18-2016, 12:21 PM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    OK, so in the new file that you have provided, please explain which totals you think are incorrect, why, and what they should be. We are nearly there, but not quite ...
    Reply With QuoteRegister To Reply
  6. 10-18-2016, 12:23 PM #6
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    ok so in the day 6 column we have 12 total hours, 8 reg hours and 4 ot hours. This should read 12 total hours 4 reg hours and 8 ot hours because now the total reg hours in the week has passed 44 hours. Day 7 should then read 12 total hours 0 reg hours and 12 ot hours. Thanks very much for your time!!
    Reply With QuoteRegister To Reply
  7. 10-18-2016, 12:26 PM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    Is this what you are after in Z8?

    =X8-Y8+IF((D8+G8+J8+M8+P8+S8+V8)>AF8,(D8+G8+J8+M8+P8+S8+V8)-AF8,0)

    which returns 52 OT hours.
    Reply With QuoteRegister To Reply
  8. 10-18-2016, 12:28 PM #8
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    No not quiet. I believe the Z8 does what I need it to. I think I need a new formula in cells S8 and V8 to max out after the sum of reg hours is greater than 44 hours or whatever value is in the AF8 cell.
    Reply With QuoteRegister To Reply
  9. 10-18-2016, 01:00 PM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    I can sort of see where you are going with this, but if you do the maxing out in both places, then your calculations will become extremely complex. Why not just work it out in the totals column? What is the benefit of seeing it 'as it happens' on a day-by-day basis?
    Reply With QuoteRegister To Reply
  10. 10-18-2016, 01:06 PM #10
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    Yeah you're probably right. It does do what I need it to for now but it would be nice to see the proper hours in each day. It's really hurting my brain right now!
    Reply With QuoteRegister To Reply
  11. 10-18-2016, 01:15 PM #11
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    Well, it's do-able, but less straightforward than doing it in the totals column!
    Reply With QuoteRegister To Reply
  12. 10-18-2016, 01:18 PM #12
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    If you make it work let me know, if not it's all good I really appreciate the responses! Definitely not a straight forward formula to use for this!
    Reply With QuoteRegister To Reply
  13. 10-18-2016, 01:45 PM #13
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    OK. Couldn't resist! Here you go:

    D8: =IF(C8>8,$AF$7,C8)
    E8: =C8-D8

    G8: =IF(D8+IF(F8>8,$AF$7,F8)>$AF$8,$AF$8-(D8),IF(F8>8,$AF$7,F8))
    H8: =F8-G8

    J8: =IF(D8+G8+IF(I8>8,$AF$7,I8)>$AF$8,$AF$8-(D8+G8),IF(I8>8,$AF$7,I8))
    K8: =I8-J8

    M8: =IF(D8+G8+J8+IF(L8>8,$AF$7,L8)>$AF$8,$AF$8-(D8+G8+J8),IF(L8>8,$AF$7,L8))
    N8: =L8-M8

    P8: =IF(D8+G8+J8+M8+IF(O8>8,$AF$7,O8)>$AF$8,$AF$8-(D8+G8+J8+M8),IF(O8>8,$AF$7,O8))
    Q8: =O8-P8

    S8: =IF(D8+G8+J8+M8+P8+IF(R8>8,$AF$7,R8)>=$AF$8,$AF$8-(D8+G8+J8+M8+P8),IF(R8>8,$AF$7,R8))
    T8: =R8-S8

    V8: =IF(D8+G8+J8+M8+P8+S8+IF(U8>8,$AF$7,U8)>=$AF$8,$AF$8-(D8+G8+J8+M8+P8+S8),IF(U8>8,$AF$7,U8))
    W8: =U8-V8

    X8: =C8+F8+I8+L8+O8+R8+U8
    Y8: =D8+G8+J8+M8+P8+S8+V8
    Z8: =X8-Y8
    Reply With QuoteRegister To Reply
  14. 10-18-2016, 01:58 PM #14
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    This is amazing!! I just had to change the T8 and W8 formulas to R8-S8, and U8-V8 respectively. I'll spend a bit of time trying to make it not work but it look like it's all good. Thank so much for doing in a few moments what I've been trying to do for about a week now ha ha!!
    Reply With QuoteRegister To Reply
  15. 10-18-2016, 02:00 PM #15
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    It was probably me just copying incorrectly. See the attached. It all works as you requested, I believe.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  16. 10-18-2016, 02:06 PM #16
    CyrilSneer
    CyrilSneer is offline
    Registered User
    Join Date
    10-18-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    Absolutely perfect. Thanks so much!!
    Reply With QuoteRegister To Reply
  17. 10-18-2016, 02:10 PM #17
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,412

    Re: Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours

    You're welcome. Glad to have resolved it for you.
    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. [SOLVED] Timesheet that calcs time in, time out, lunch used, reg hours, ot hours, double hours
    By noobface in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-21-2021, 03:45 AM
  2. Recording Payroll Hours
    By Jayberriez in forum Excel General
    Replies: 18
    Last Post: 03-23-2016, 02:58 PM
  3. Make a cell blink by it's entered time plus 2 hours
    By clogik in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2014, 05:37 AM
  4. Payroll time sheet calculation where time exceeds 24 hours
    By Rolo1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-23-2014, 08:57 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 hours
    By Donald in forum Excel General
    Replies: 1
    Last Post: 12-13-2005, 07:15 AM
  7. [SOLVED] Payroll hours
    By Sarah B. in forum Excel General
    Replies: 4
    Last Post: 03-22-2005, 02: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