Payroll formula ?

**RJohnston** · 12-03-2006, 10:41 PM

How do i get my reg hours cell to cap out at 40 then start a new value sequence starting from 1 in the overtime hour cell. Is there a formula i can use for that or is there more to it?

**jtp** · 12-03-2006, 11:35 PM

Do you have a source cell where it shows the total hours worked or are you putting the total hours worked in the "Regular Hours" cell and then wanting the adjustments to be made in that same cell and the OT cell?

**RJohnston** · 12-03-2006, 11:40 PM

I have the reg hour cell configured, but what i need is for a specific cell (reg hours) to stop adding at 40 and then automatically put everything over 40 in the ot hour cell starting at 1.

**jtp** · 12-03-2006, 11:46 PM

This specific "Regular Hours" field, is it a manual input cell or is it pointing to another cell. I ask this because in my eyes its the difference between 2 simple formulas or a macro.

If its pointing to another cell, you can easily put a formula in this field to get the hours and stop at 40 and then take the difference and put that in the OT cell.

However if the Regular hours cell is a manual input, then you can't put a formula there so you would need a macro to make this work. Either way is not difficult.

**RJohnston** · 12-04-2006, 12:26 AM

The reg hours cell is the sum of 6 other cells not a manual input.

**jtp** · 12-04-2006, 12:33 AM

Ok if thats the case then you can use something like this in the regular hours field. I am going to say that your data is in Range A1 - A6 and this formula is in A7:

=IF(SUM(A1:A6)>40,40,SUM(A1:A6))

The OT cell is in B7

=IF(A7=40,SUM(A1:A6)-40,0)

You can of course make the adjustments as needed.

HTH