Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

1. Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

I'm having trouble with my Overtime Hours column (I).
I can't figure out how to get it to show just the overtime for that day.
I have attached the xlsx file as well as a photo of the table as is.

The formula for that column is: =IF(ISBLANK(D20),0,(MAX(H20-40,0)))
I want it to subtract the previous cell before it, but every time I do it returns a value error with this formula:
=IF(ISBLANK(D20),0,(MAX(H20-40,0)-I5:I6)) Then the I6 increases

The ultimate goal is for a spreed sheet that can calculate employee wage with a single employee that has two separate wages
that shares the same set of 40 hours for that week in a biweekly setup with an on call bonus added at the end of the week. If I can get this overtime figured out I think I got the rest of it. I might have trouble with the job codes I'm not sure yet. I'm going to change them to numbers when I get to them, the current strings are for easy reference at this point.

2. Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

is this the formula that you are looking for? Paste in I5 and fill down

Formula:
`Please Login or Register  to view this content.`

3. Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

No that didn't do it, thanks for the fast response though.

Essentially I14 is correct in saying that there is only 1 hour of overtime for that day, but I18 is wrong in saying 9.25 hours of overtime was calculated when it should only say 8.25. They both use the same formula. What I have might be completely wrong.

What i have right now is giving me the difference after 40 has been reached in column I. I don't want that. I want what would be overtime for that day in column I once 40 has been reach at some point during the week. I have to do it this way since the employee is getting different wages depending on what they do that day.

4. Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

=IF(ISBLANK(D5),0,MAX(H5-40,0)-SUM(\$I\$4:I4))

Drag down

5. Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

Awesome. That did it. Thank you both.

6. Re: Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis

Hello All,

HumdrumPanic's original post/question is precisely my current issue with my payroll spreadsheet.

I would like to access the link's HumdrumPanic posted in the first post but I believe I am lacking privilege's/access?

Apologies if this is the incorrect pathway for resolving my issue.

I appreciate and thank you for helping me with this issue.

There are currently 1 users browsing this thread. (0 members and 1 guests)