# Calculating overtime to be paid after deductions are made

1. ## 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

2. ## 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)))

Problem 2 (I think):

=IF(H4=0,G4,0)

3. ## Re: Calculating overtime to be paid after deductions are made

Hi AliGW,

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

4. ## 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)

5. ## 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

6. ## Re: Calculating overtime to be paid after deductions are made

Try

in J4

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Calculating overtime to be paid after deductions are made

JohnTopley's formula in 365 version.
Single formula in J4
Formula:
`Please Login or Register  to view this content.`

8. ## 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

9. ## 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

11. ## 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)))))

12. ## 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

13. ## 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. ## 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. ## 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. ## Re: Calculating overtime to be paid after deductions are made

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

17. ## Re: Calculating overtime to be paid after deductions are made

You're welcome. Thanks for the rep!

18. ## Re: Calculating overtime to be paid after deductions are made

Hi AliGW,

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

19. ## 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. ## 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

21. ## 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. ## 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. ## 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

24. ## 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. ## 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

26. ## Re: Calculating overtime to be paid after deductions are made

Thanks for letting me know.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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