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

[taylorsm]

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.

[p24leclerc]

Put this formula in cell G20

Please Login or Register  to view this content.

and this one in cell G21

Please Login or Register  to view this content.

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?

[taylorsm]

That doesn't seem to work. I just end up with 7.3833333333 in G20 and .62 in G21.

And while highly unlikely that someone could get 40 hours before wednesday/thursday, it is possible and so I like to just have it in place in the off chance it happens. Think of a nurse or someone that works 12 hour shifts. Sunday/Monday/Tuesday is 36, so Wednesday they would hit 40 hours.

I uploaded the spreadsheet again with your formulas in their respective positions.

[Indi_Ra]

You have to format the cell,

See attach

[taylorsm]

Thanks, yeah sorry. Formatting isn't the issue. Your attachment doesn't do anything different. The numbers aren't rounded. I added an extra couple rows with how the data should look.

[taylorsm]

I tried this, i think i'm in the ballpark? strange thing is that i have a "IF<40" and it is being activated if it is exactly 40.

This is what the formula actually is. I had to make it 40.01 because some reason excel wasn't wanting to rounddown even if the number was 40 exactly.

Please Login or Register  to view this content.

[JeteMc]

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:

Please Login or Register  to view this content.

Let us know if you have any questions.

[taylorsm]

Thanks so much, but I think something is still a little off. I think your regular hours is good, but the OT is off. It is taking what is in B24 and x2 it. Open up the workbook and delete B24, you'll see B21 go from 4 down to 0.

[JeteMc]

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

[JeteMc]

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:

Please Login or Register  to view this content.

The following formula is applied to row 18:

Please Login or Register  to view this content.

The following formula is applied to row 20:

Please Login or Register  to view this content.

The following formula is applied to row 21:

Please Login or Register  to view this content.

Let us know if you have any questions.

[taylorsm]

Thanks. I don't know about the A7:A13 change, it was probably from a copy and paste, but that shouldn't affect what I am trying to accomplish. The workbook doesn't subtract from row 24. Row 20 should subtract Row 24. I added a note in the workbook to show what I mean. Thanks again!

[JeteMc]

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.