Timesheet calculator to prevent sum to be over 40, and have the extra roll into OT

We have a punch in/out system that records exact time and then a payroll system that rounds that exact time. (stupid IMO, but see attachment for visual)

Say you work this below. Second set of times is just rounding the first to make it easier to know what to input (it also allows my employees to keep track of what they actually worked vs paid for) Anyways, the regular hours has to cap at 40, so if you round up then the 7.4 should really end up being a 7.3 because once you input, you hit 40 hours at 7.3.



True Hours - 8.17 8.07 8.23 8.15 7.38 -
True OT - - - - - 0.62 -


6130-001 Regular - 8.2 8.1 8.2 8.2 7.4 -
6130-001 OT - - - - - 0.6 -


Sorry, I i know that wasn't real clear. Hoping the attachment makes more sense. Actual hours are then rounded to nearest .1 and anything over 40 is rolled into OT.

Put this formula in cell G20

=IF(SUM(B20:F20)+ROUND((G17-G24),1)>40,40-SUM(B20:F20),ROUND((G17-G24),1))

and this one in cell G21

=ROUND(G18,1)+ROUND(G17+G24,1)-G20

I'm not sure if you'll have to use the same formule in columns E and F, though.
Can someone reach the 40 hours before wednesday or Thursday?

This is addressing the formulas that p24leclerc wrote as applied to the original file attached to post #1. They seem to work perfectly well. I did modify the first one so that it could be pasted into cell B20 and dragged across. The formula written for G21 just needs to be copied to the left (B20) as shown in the attached copy of the file.
The modified first formula reads:

Formula:

=IF(SUM($A20:A20)+ROUND((B17-B24),1)>40,40-SUM($A20:A20),ROUND((B17-B24),1))

Let us know if you have any questions.

Should the values in rows 23:25 be taken into consideration when calculating values in rows 17:18 and 20:21? If so how?

I am going ahead and uploading this as I will be away for a few days.
In the attached file A7:A13 have been changed and the formatting applied so that they appear to be the text entries that were there before.
The following formula is applied to row 17:

Formula:

=IF(SUM($A17:A17)>=40,0,IF(SUM(SUMPRODUCT(($A7:$A13=B16)*($F7:$F13)),$A17:A17)>40,40-SUM($A17:A17),SUMPRODUCT(($A7:$A13=B16)*($F7:$F13))))

The following formula is applied to row 18:

Formula:

=SUMPRODUCT(($A7:$A13=B16)*($F7:$F13))-B17

The following formula is applied to row 20:

Formula:

=IF(SUM($A20:A20,B17)>40,40-SUM($A20:A20),ROUND(B17,1))

The following formula is applied to row 21:

Formula:

=ROUND(SUM(B17-B20,B18),1)

Let us know if you have any questions.

Try the following:
1) Paste the following formula into B20 and drag across to H20: =IF(SUM($A20:A20,B17)>40,40-SUM($A20:A20)-B24,ROUND(B17,1)-B24)
Note: delete the text in cell D24
2) Paste the following formula into B21 and drag across to H21: =ROUND(SUM(B17-SUM(B20,B24),B18),1)
Let us know if you have any questions.