Help with timesheet, need formula for calculating hours

pink,

This may work for you:

I entered hours worked in column B, then computed regular hours in Column C, OT hours in column D, then Double Overtime hours in column E:

C2=MIN(B2, 8)

D2=MIN(B2−C2, 4)

E2=B2−C2−D2

select C2 thru E2 and fill down as needed by hovering the cursor over the bottom edge of the selection and draggin the yellow circle down as needed.

pinkwaves013 wrote:

Thank you, that makes sense and helps, but I need to keep the DUR2HOURS part in there..

Is there a way to adjust what I have now slightly to make it work?

=MAX(DUR2HOURS(T21)-8, 0)

No.

You can't use functions inside of the MAX function. The only allowed arguments for MAX are cell ranges.

Jerry

Pink,

You will save yourself troubl if you simply enter the duration you worked in the format "XXh YYm" like:

"4h 30m"

when you enter a time (or date) Numbers actually makes up the the portion of the Time/Date value you did not enter. So if you enter "9:00A", numbers actually stores "12/09/2013 9:00A" or what ever date you entered the time. This makes for a real mess later if you decide to change the time.

Maybe this will help:

In column A enter the duration you worked.

B2=DUR2HOURS(A2)

fill down as before

PW,

My answer was a bit severe. I misread your question as the common one where there is an attempt to avoid adding a column for calculations.

You can, for instance, do this:

which I believe is close to your requrest.

Jerry

Pink,

Maybe this will work:

C4=IF(AND(LEN(B6)>0,LEN(B7)>0),B7−B6, "")

select C4, copy, paste to C7, C10 and C13

C14=SUM(C4,C7,C10,C13)

C15=IFERROR(DUR2HOURS(C14), 0)

C17=MIN(C15, 8)

C18=MIN(C15−C17, 4)

C19=MIN(C15−C17−C18)

select C4 thru C19, copy, now paste into the corresponding locations for the rest of the days of the weeks (E4, G4, I4, K4, M4, O4)