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

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

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

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

Hi kvsrinivasamurthy,

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

Cheers .. Michael

Attachment 872294

Hi AliGW,

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

Cheers .. Michael

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

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

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

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

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

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