Time Sheet Calculating Daily Hours Up To 40 Hours

[amelchor]

Hi I have been trying to figure this out for weeks. I need my total daily hours in my regular hours column to only calculate up to 40 hours then anything over 40 hours to total in in the overtime cell. Right now it just totals the total work hours worked for a day in the regular hours, but I only want it to do up to 40 hours that way it only reflects regular hours then anything over that totaled in the overtime hours cell.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]Regular[/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Monday
[/TD]
[TD]04/23/18[/TD]
[TD]8:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]4:30 PM[/TD]
[TD]7.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Tuesday[/TD]
[TD]04/24/18[/TD]
[TD]8:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]5:30 PM[/TD]
[TD]8.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Wednesday[/TD]
[TD]04/25/18[/TD]
[TD]8:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]8.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Thursday[/TD]
[TD]04/26/18[/TD]
[TD]8:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]8.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Friday[/TD]
[TD]04/27/18[/TD]
[TD]8:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]5:15 PM[/TD]
[TD]8.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Saturday[/TD]
[TD]04/28/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Sunday[/TD]
[TD]04/29/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]40.00[/TD]
[TD]0.25[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tom Urtis]

In cell H19
=MIN(SUM(H12:H18),40)

In cell I19
=MAX(0,SUM(H12:H18)-40)

 

[amelchor]

Thank you for your response Tom! What I am looking for is I want my daily regular hours in each cell to only calculate until all of those cells hit 40 hours. By the time the regular hours gets to H16 the 0.25 over 8 hours is considered overtime, so I have the 0.25 populating in the overtime cell, but that 0.25 still reflects in my daily regular hours. I would want that H16 to state 8.00 hours since the week exceeded 40.00 hours.

 

[Tom Urtis]

It would have helped if your graphic showed that objective instead of all cells in column I except cell I19 being blank. Your question is solve-able, but please clarify 2 points:

(1)
That the workweek begins on Monday and if someone in a given week does not work on Monday, Tuesday, Wednesday, Thursday, or Friday, but in that calendar week they work 15 hours on Saturday and 15 hours on Sunday, and then the following calendar week (which is the next day) they work 15 hours on Monday, then at that moment there is no overtime involved.

(2)
That there is no consideration for daily overtime of more than (for example) 8 hours of work in a single day.

 

[amelchor]

The work week starts is from Monday - Sunday. There is only overtime once someone has worked over 40 hours for the work week. Even if someone works over 8 hours a day it is not considered overtime. I would like for the regular daily hours to only populate up to 40 hours then after that anything over 40 hours populate in I19

 

[Tom Urtis]

Just to clarify further because something seems odd; your reply will determine the necessary formula(s), you wrote:
"...anything over 40 hours populate in I19"
The second formula in my first reply does that already.

Your original image shows 8.25 in cell H16 but you show nothing in cells I12 to I18 (the Overtime column).
Is that really what you want -- nothing in cells I12:I18 and only a conditional total of overtime beyond 40 if the sum in range H12:H18 is greater than 40?
If so, that would mean you really want 8.25 in cell H16 and not 8.00 in H16 and .25 in cell I16?
It struck me as odd that you show, and seem to reinforce by saying, you only want the total overtime cell (I19) to be possibly occupied, and all the worked time to be in H12:H18.

Either way is fine by me, it's your worksheet, just making sure I get what your final result shall be, visually as well as mathematically.