# Calculating Overtime Hours based on 40 hour work week

1. ## 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!  Register To Reply

2. ## Re: Calculating Overtime Hours based on 40 hour work week

Where are the hours entered? Since a formula can only evaluate and show a result, you cannot input the hours in column A. Instead, I'd insert a column, and enter the hours in column A, and then Column B would be base issues, and C would be OT hours.  Register To Reply

3. ## Re: Calculating Overtime Hours based on 40 hour work week

Hey Obsessed,

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!  Register To Reply

4. ## Re: Calculating Overtime Hours based on 40 hour work week

Try

in J9

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

in J18

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

Copy both down  Register To Reply

5. ## Re: Calculating Overtime Hours based on 40 hour work week

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   Register To Reply

6. ## Re: Calculating Overtime Hours based on 40 hour work week

Here you go...this should take care of you. I had to remove the "-" and replace it with "0", but you can change the cell formatting to show zeros as such. Just copy the formulas I put in Week 1 down on week 2 as well.  Register To Reply

7. ## Re: Calculating Overtime Hours based on 40 hour work week

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.  Register To Reply

8. ## Re: Calculating Overtime Hours based on 40 hour work week

Basically, the logic behind the formula is "If today's hours plus previously logged hours above is greater than 40 hours, then today's hours should be 40 - sum of hours above" and then the OT formula basically evaluates "If today's hours = today's hours shown in the column, then OT = 0, otherwise, it's the difference."   Register To Reply