Timesheet not calculating properly. Looking for help!

[GSPProd]

Hi everyone!

I have been working on this timesheet document for a while now and cannot seem to get the right formula for my goal.

Very simply, the document has a row for each day of the week. For each day, the fake employee fills out when they began and ended their shift and if they took any breaks. Column G is to add up all the hours between when they started and ended, while Column H then subtracts any break time they had.

H11 is then used to calculate their weekly hours. This is where I run into my issue. I'd love for H11 to carry down to B12, but B12 be capped at 40 hours. Any time an employee goes over 40 hours, we obviously owe them OT. So in this example, the employee worked 46 hours, and 40 of them go into B12, but I can't seem to get the remaining six hours to be dropped into B13. I'm not sure if this is a time vs decimal point issue. I'd love for the G and H columns to be decimal points (5.5 hours vs 5:30 hours) but I couldn't get it to calculate properly with decimal points either.

I've gotten it to the point where only B13 is my problem cell. Anyone able to help get me across the finish line?

[Pepe Le Mokko]

Hi
you posted on the XL subforum but your profile states Google Sheets. Is it a GS question then?

[GSPProd]

Hi Pepe Le Mokko! Google Sheets is what I started the document in, however I uploaded it is an Xlsx, so I wasn't sure which forum to drop it in. I also wasn't sure if Xslx vs GS was an important distinction for this particular question. Apologies if it should have ended up somewhere else!

[HansDouwe]

Please try in B13:

Please Login or Register  to view this content.

[HansDouwe]

Or try

Please Login or Register  to view this content.

[GSPProd]

HansDouwe, if I lived in Nederland with you, I'd buy you a beer. It seems your suggestion has fixed that issue, but now I'm seeing my original formula in B12 wasn't correct. I've attached a new spreadsheet to this comment. In this new example, even though this fake employee only worked 39.5 hours, 40 hours is being carried over to B12. Is there a way to fix this so that if an employee doesn't work over 40, the proper number in H11 is shown in B12?

[Pepe Le Mokko]

In B12 try

Please Login or Register  to view this content.

[GSPProd]

Pepe Le Mokko, if I end up in Belgium one day, I also owe you a beer. Cannot thank you and HansDouwe enough. I can finally... FINALLY... put this project to rest. If I got anything else that comes up with this spreadsheet, I'll be sure to give either of you a buzz. Thanks again!!

[Pepe Le Mokko]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon 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 those who helped.

[AliGW]

If you are using Excel, please update your forum profile to show which version you are on.