Overtime Formula Help

[gdillon]

I need a simple formula fix here, On a single timecard when the time worked is entered daily and overtime kicks in only after 40 hours. I track the time in each day and the total hours worked. What formula can I use that will automatically separate regular time and overtime. Lets say this person worked 12, 12, 12, 12 that is 48 hours I would like to track this daily and to be correct it would be 12, 12, 12, 4, with overtime at 8 hours

I want to have (2) cells at the end of each day one that captures regular time and the other that captures overtime once the gross total week has exceed. So in this example 12, 12, 12, 4 would show in the cell for regular hours and the 8 would show in the cell for OT hours.

 

[Domski]

Hi,

Not sure this is exactly what you're after but:

Book3
	A	B	C	D	E	F	G
1	Shift1	Shift2	Shift3	Shift4	Shift5	RegularTime	Overtime
2	12	12	12	12		40	8
Sheet1

Hope it helps,

Dom

 

[VoG]

Assuming regular hours is 36 then for regular hours

MIN(36,SUM(A1:C1))

and for overtime

=IF(SUM(A1:C1)<=36,0,SUM(A1:C1)-36)

 

[dave3009]

Hi There

Lets assume one persons hours run B2:H2 for the days of the week

in I2 the formula is =IF(SUM(B2:H2)>40, 40, SUM(B2:H2))

Then in J2 formula is =IF(I2=40, SUM(B2:H2)-40, 0)

This gives regular hours to 40 in column I and overtime in J

Does this work, you will need to adjust ranges to suit

Kind Regards


Dave