Calculating Overtime Hours based on 40 hour work week

Hi There,

Have a question whether this is possible with a formula or I will need to use a VBA solution.

I have two columns on a time sheet, column A is regular work hours column B is Overtime hours.

I have trying to find a way that when the sum of column A hits 40.00 to start accruing hours in Column B

Column A	Column B
8.21	-
8.14	-
8.58	-
8.34	-
6.73	1.52
40.00	1.52

Any Ideas? I can't seem to figure out anything that wouldn't be a nightmare of a formula.

Thanks!

Hey Obsessed,

Thanks for replying.

I have attached the workbook I am working on with some sample data, you can see that the "Regular hours" already has a formula in it to derive hours from the punch times. I was hoping to modify the formula to achieve the results I was displaying above.

PayrollTemplate.xlsx

If you could take a look at it that would be great!

Thanks!

Try

in J9

=MAX(SUM($I$9:I9)-40,0)

in J18

=MAX(SUM($I$18:I18)-40,0)

Copy both down

Thanks Overtime!

That fixes one part of my problem!

The second part would be I would want cell J15 to read 7.44 and the total "worked hours" to read 40.

Was hoping there would be a formulaic way to "Stop" accruing time in whatever cell in the range put the total over 40 and put any excess time over to the Overtime column.

Sorry for the clumsy explanation

Thanks so much!

That works perfectly, I'll have to study this as I was having a hard time putting something together that wasn't a circular reference.

Thanks again for all of your help!!

Marking as Resolved.