+ Reply to Thread
Results 1 to 26 of 26

Calculating overtime to be paid after deductions are made

  1. #1
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Calculating overtime to be paid after deductions are made

    Dear Forum members,

    I work a 40-hour week. 38 hours are paid, and 2 hours are retained for a monthly allocated day off.

    The award states that a minimum of 38 hours of normal time must be allocated, and any remaining time needed to make up the 40 hours can be taken from any overtime worked that week.

    I have 2 problems that I hope you can help me with.

    Problem 1. I wish to show cumulative time as zero when the 40 hours are reached.
    Problem 2. I need a formula that will then show any overtime worked and to be paid after the 40 hours are reached.

    I have attached a spreadsheet. Please feel free to make any changes or suggestions to this sheet that you deem appropriate.

    The result I want is to be able to calculate any leftover time and add that to my pay.

    Thank you .. Michael

    Screenshot 2024-06-13 125226.png
    Attached Files Attached Files

  2. #2
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    Problem 1 - does this work for you?

    =LET(sa,SUM(G4,E8),sb,SUM(H3,G4),IF(A4="Monday",sa,IF(ISNUMBER(XMATCH(40,H$3:H3,0)),0,sb)))

    Please test it.

    Problem 2 (I think):

    =IF(H4=0,G4,0)
    Last edited by AliGW; 06-13-2024 at 02:42 AM.
    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.

  3. #3
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGW,

    Thanks for your help.

    Problem 1. I did a test, and I increased cell G6 from 0.25 hrs to 0.35hrs, and unfortunately, the cumulative time in cell H6 increased accordingly, and did not remain as zero, which is also shown in cell H7.

    As for Problem 2. Your formula works as shown in J8 when H8 is 0.00. However, when H6 increased in time with the Problem 1 test to 40.10, J6 should pick up the 0.1 hrs to be paid.

    I have attached a screenshot and a revised spreadsheet.

    Thanks once again for your extremely kind assistance.

    Cheers ... Michael

    Attachment 872060
    Attached Files Attached Files

  4. #4
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    Try this:

    =LET(sa,SUM(G4,E8),sb,SUM(H3,G4),IF(A4="Monday",sa,IF(ISNUMBER(XMATCH(40,H$3:H3,0)),0,MIN(40,sb))))

    Then this works:

    =IF(H4=0,G4,0)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGW,

    There still is a problem with Column J.

    I should be paid the balance if the overtime worked is greater than 40 hours for that day. For example, on Tuesday I should be paid a balance of 0.25 hours.

    I tried doing several different formulas to correct it, but I do not have your skill level, so I still need your help

    Cheers ... Michael

    Attachment 872135
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,848

    Re: Calculating overtime to be paid after deductions are made

    Try

    in J4

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,698

    Re: Calculating overtime to be paid after deductions are made

    JohnTopley's formula in 365 version.
    Single formula in J4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi John,

    Thanks for your help mate. Unfortunately it still has not solved my problem.

    I have attached a screenshot and a revised file for you.

    Cheers .. Michael

    Attachment 872292
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi kvsrinivasamurthy,

    Thank you for assisting me. Please refer to the attached screenshot, unfortunately the formula does not work.

    Cheers .. Michael

    Attachment 872294

  10. #10
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    Your visual attachments are invalid.

  11. #11
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    Try this:

    =MAP(G4:G8,H4:H8,LAMBDA(x,y,IF(y=0,x,IF(y<40,0,MAX(0,H4+SUM(G4:x)-40)))))
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGw,

    Thank you. I have tested different times in your spreadsheet, and it is amazing what you have done. Your formula gives the required result I needed. I truly am in awe of your knowledge.

    But I have 2 queries;

    1. How do I transfer your formula into my main spreadsheet, as it is a 2-week pay period? For the forum post I did a model of Monday - Friday over a 1-week period to keep it as basic as possible, and
    2. For my further education, and if agreeable to you, can you explain the formula and how it is derived?

    Cheers .. Michael
    Last edited by Mike Geraghty; 06-18-2024 at 11:08 PM.

  13. #13
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGW,

    Sorry, but I do not understand what you mean by the attachments as being invalid.

    Cheers .. Michael

  14. #14
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi John,

    I'm sorry for not getting back to you sooner. I have been bogged down with work and personal issues.

    Anyway, thanks for your help. By combining your formula with AliGw's formula my problem has been solved, so it is very much appreciated mate.

    Cheers ... Michael

  15. #15
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGw,

    I'm sorry for not getting back to you sooner. I have been bogged down with work and personal issues.

    Anyway, thanks for your help. I was able to work out what I should do with your formula and success !!! Thank you for being so patient. Also JohnTopley's support was appreciated.

    This problem has been solved, so it is very much appreciated.

    Cheers ... Michael

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,848

    Re: Calculating overtime to be paid after deductions are made

    Thank you for the rep but all credit is due to Ali.

  17. #17
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    You're welcome. Thanks for the rep!

  18. #18
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGW,

    I still need your help

    I jumped the gun thinking all was OK, until I entered in your formula into a full 2 week work period in my spreadsheet.

    The problem that has surfaced is that if the normal hours worked are greater than 40, I need the cumulative time on the overtime columns as zero.

    As you can see Week 1, being below 40 hours normal time worked, is no problem and all cells for that week are correct.

    However, in Week 2, as the normal hours are greater than 40, the cumulative time does not revert to zero, as shown in cells F18 and F19. After that it is OK and all cells show the correct result.

    I have attached a revised spreadsheet.

    Thanking you once again, and I do hope I have entered in your formula correctly ... Michael


    Attachment 872911
    Attached Files Attached Files

  19. #19
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    I don't think you ever told me this.

    Try this:

    =LET(sa,SUM(E$18,D$24),sb,SUM(F17,E18),IF(sa>40,0,IF(B18="Monday",sa,IF(ISNUMBER(XMATCH(40,$F17:F17,0)),0,MIN(40,sb)))))

  20. #20
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi AliGW,

    I have been putting in different times as a test, and I still have a problem with the calculation. I do hope I followed your formula correctly, and I still feel I could be the problem

    Week 1 is not correct but week 2 is correct. When I change the normal hours worked and the overtime worked, it seems the calculation is correct when Normal Hours worked is over 40 hours, but if it is less than 40 hours there is an incorrect calculation.

    I have also attached the spreadsheet in the same format as the spreadsheet in my workbook, without the time links.

    Cheers ... Michael

    Attachment 873310
    Attached Files Attached Files

  21. #21
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    You have not indicated what is right and what is wrong. Please highlight incorrect values and add a manual column showing the values you are expecting.

  22. #22
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    I think it's you. As far as I can see, this:

    =LET(sa,SUM(F$7,D$13),sb,SUM(G6,F7),IF(sa>40,0,IF(B7="Monday",sa,IF(ISNUMBER(XMATCH(40,$F6:F6,0)),0,MIN(40,sb)))))

    should be this:

    =LET(sa,SUM(F$7,D$13),sb,SUM(F7,E8),IF(sa>40,0,IF(B7="Monday",sa,IF(ISNUMBER(XMATCH(40,$F6:F6,0)),0,MIN(40,sb)))))

    If this isn't what you want, do what I asked in post #21.

  23. #23
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi Ali,

    Thanks for the suggestion, I tried it but had no success.

    So attached is a revised sheet as per your suggestion in Post #21

    When we compare Week 1 which is less than 40 hours worked to Week 2, which is greater than 40 hours, the problem I am still having is in Week 1. As you can see Week 2 is OK.

    Also, please keep in mind that the numbers highlighted in bold red font are correct calculations.

    Thanks once again for your patience, perseverance and continued help for me.

    Cheers ... Michael

    Attachment 873394
    Attached Files Attached Files

  24. #24
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    I'm sorry - I'm completely lost on this. Someone else will have to step in.

    I suggest you remove the SOLVED tag to attract attention back to the thread.

    I am afraid I'm out.

  25. #25
    Registered User
    Join Date
    03-24-2022
    Location
    Sydney, Australia
    MS-Off Ver
    MS365 Version 2210
    Posts
    57

    Re: Calculating overtime to be paid after deductions are made

    Hi Ali,

    I also left it hoping to find some solution, and to let you know we have!

    I had to walk away from my workbook for a while and clear my head, hence the delay in replying to you.

    I knew your formulas were correct and thought there must be a problem with how cumulative time was captured.

    Bear in mind every week may have differing work times.

    So here is a screenshot of the solution I came up with that works. It looks messy but it works. I also had to tweak your Monday cumulative time formula.

    I have also attached the spreadsheet that shows 2 different weekly scenarios for the pay period.

    With your incredible input, we have succeeded

    So I will keep the SOLVED tag as it is.

    In conclusion, thank you for your effort. It is sincerely appreciated.

    Cheers ... Michael

    Attachment 874011
    Attached Files Attached Files

  26. #26
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Calculating overtime to be paid after deductions are made

    Thanks for letting me know.

    Glad to have helped.

    If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

+ 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] Help with formula for calculating deductions
    By fordtough68 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-04-2022, 05:33 PM
  2. [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
  3. Replies: 7
    Last Post: 08-14-2013, 07:04 PM
  4. [SOLVED] Help Calculating Deductions In A Single Cell
    By nevintech in forum Excel General
    Replies: 2
    Last Post: 08-13-2012, 04:15 PM
  5. Replies: 5
    Last Post: 04-16-2012, 08:12 PM
  6. Calculating deductions from gross income?
    By fredlikes in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-06-2010, 05:12 PM
  7. calculating deductions
    By Kmac in forum Excel General
    Replies: 13
    Last Post: 03-31-2010, 05:09 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