To simplify using my timesheet I am trying to use multiple functions so all
that I need to do is enter the start and finish times and it will
automatically fill in my standard and overtime rates.

I currently have the formula =IF(F10<=10,F10,10) in my standard rate column
where F10 is my hours worked that day. My overtime column has the formula
=IF(F1010,F10-10,"") and this works to tell me any overtime on a day to day
basis and keeps the sheet fairly clean.
However as well as going into overtime if I work more than 10 hours day, I
also go into overtime if I work more than 40 hours in a week so ...

I want to add an additional argument where if the sum of a column becomes
greater than 40, the additional moves into the next column. For example if I
had four 8s and then a 10, the ten would be split into an 8 which would
remain in the column and a 2 would be transferred to the overtime column in
the cell for that day
It would be nice also if the column once the column total reached 40, any
further days would go blank.
Please note that days worked and hours per day can vary week to week so
values need to based on the F10 figure or the overtime limits.

I'm guessing that I'm headed towards circular functions with this and any
help would be appreciated

Assuming that the daily hours are in F10:F16, and the the regular hours
are in G10:G16, then use
=MIN(F10,10)
in G10,
=IF(SUM(G$10:G10,MIN(F11,10))40,40-SUM(G$10:G10),MIN(F11,10))
in G11, and copy G11 and paste over G12:G16. Then get overtime hours by
subtraction.

Jerry

TeeJay wrote:

To simplify using my timesheet I am trying to use multiple functions so all
that I need to do is enter the start and finish times and it will
automatically fill in my standard and overtime rates.

I currently have the formula =IF(F10<=10,F10,10) in my standard rate column
where F10 is my hours worked that day. My overtime column has the formula
=IF(F1010,F10-10,"") and this works to tell me any overtime on a day to day
basis and keeps the sheet fairly clean.
However as well as going into overtime if I work more than 10 hours day, I
also go into overtime if I work more than 40 hours in a week so ...

I want to add an additional argument where if the sum of a column becomes
greater than 40, the additional moves into the next column. For example if I
had four 8s and then a 10, the ten would be split into an 8 which would
remain in the column and a 2 would be transferred to the overtime column in
the cell for that day
It would be nice also if the column once the column total reached 40, any
further days would go blank.
Please note that days worked and hours per day can vary week to week so
values need to based on the F10 figure or the overtime limits.

I'm guessing that I'm headed towards circular functions with this and any
help would be appreciated

Thanks for the prompt response.
The formula is nearly there but isn't quite what I'm looking for. Is there
someway to have the column sum figure overrule the individual cell entry ie
if I was to work a day of nine hours, having already done 35 hours that week
could I have a formula that would enter 5 into the column rather than either
moving the whole value across or leaving the field blank.

I currently have =IF((SUM(G$10:G10)<=40),MIN(F10,10),"") for the standard
hours and =IF((SUM(H$10:H10)40),F10,IF((F1010),F10-10,"")) for the overtime
hours but it get a little confused at the 40 hour mark

"Jerry W. Lewis" wrote:

Assuming that the daily hours are in F10:F16, and the the regular hours
are in G10:G16, then use
=MIN(F10,10)
in G10,
=IF(SUM(G$10:G10,MIN(F11,10))40,40-SUM(G$10:G10),MIN(F11,10))
in G11, and copy G11 and paste over G12:G16. Then get overtime hours by
subtraction.

Jerry

TeeJay wrote:

To simplify using my timesheet I am trying to use multiple functions so all
that I need to do is enter the start and finish times and it will
automatically fill in my standard and overtime rates.

I currently have the formula =IF(F10<=10,F10,10) in my standard rate column
where F10 is my hours worked that day. My overtime column has the formula
=IF(F1010,F10-10,"") and this works to tell me any overtime on a day to day
basis and keeps the sheet fairly clean.
However as well as going into overtime if I work more than 10 hours day, I
also go into overtime if I work more than 40 hours in a week so ...

I want to add an additional argument where if the sum of a column becomes
greater than 40, the additional moves into the next column. For example if I
had four 8s and then a 10, the ten would be split into an 8 which would
remain in the column and a 2 would be transferred to the overtime column in
the cell for that day
It would be nice also if the column once the column total reached 40, any
further days would go blank.
Please note that days worked and hours per day can vary week to week so
values need to based on the F10 figure or the overtime limits.

I'm guessing that I'm headed towards circular functions with this and any
help would be appreciated

D'oh

Didn't read Jerry's response properly. He's absolutely right.
I was just getting too caught up in IF arguments to rememebr to K.I.S.S.

TeeJay