Issues with Shift Differential/OT Formulas (table included)

[azepeda88]

	A	B	C	D	E	F	G	H
1	name	Start time	End Time	Actual Hrs	Straight	Shift Diff	OT	Shift Diff OT
2		02/24/2018 06:10	02/24/18 18:40	12	12	.7
3		02/25/2018 06:10	02/25/18 18:40	12	12	.7
4		03/02/2018 06:10	03/02/18 18:40	12	12	.7
5		03/03/2018 06:10	03/03/18 18:40	12	12	.7
6				48	40	2.8

<tbody>
</tbody>

OK, having issues with formula or trying to figure out how to automate with formulas, the table above is what i have working so far. I've been working on this for a week and cant figure out formulas that need to use. Thank you in advance.

- E5 needs to change to automatically not go over 40 hrs and the remainder need to go into G5
ex. if 12 is imputed in E5 it should auto change to 4 and put the remaining 8 to G5 as OT
- when OT is incurred as in E5 the shift diff in F5 needs to be placed into H5 instead to be payed as Shift Diff OT
- shift diff will be imputed manually in F2-F5

 

[spiralrain]

I think this is what you're looking for:

Code for Cell D2:


=IF((C2-B2)>40,40,C2-B2)


Code for Cell D3:
=IF(C3-B3+SUM($D$2:D2)>40,40-SUM($D$2:D2),C3-B3))


Drag the code down through all of the part of column D that you'll be using.


Code for G2:


=IF(C2-B2=D2,0,C2-B2-D2)


Drag the code down through all of the parts of column G that you'll be using.


Code for H2:


=IF(G2=0, "", F2)


Drag the code down through all of the parts of H that you need it.


Hopefully that's what you need, or will at least get you closer to what you need.

 

[trortr213]

I think this is what you're looking for:

Code for Cell D2:


=IF((C2-B2)>40,40,C2-B2)


Code for Cell D3:
=IF(C3-B3+SUM($D$2:D2)>40,40-SUM($D$2:D2),C3-B3))


Drag the code down through all of the part of column D that you'll be using.


Code for G2:


=IF(C2-B2=D2,0,C2-B2-D2)


Drag the code down through all of the parts of column G that you'll be using.


Code for H2:


=IF(G2=0, "", F2)


Drag the code down through all of the parts of H that you need it.


Hopefully that's what you need, or will at least get you closer to what you need.

Can you modify this for overnight (6m-6m) using military time with decimals (11:45pm=23.75)?

 

[trortr213]

I think this is what you're looking for:

Code for Cell D2:


=IF((C2-B2)>40,40,C2-B2)


Code for Cell D3:
=IF(C3-B3+SUM($D$2:D2)>40,40-SUM($D$2:D2),C3-B3))


Drag the code down through all of the part of column D that you'll be using.


Code for G2:


=IF(C2-B2=D2,0,C2-B2-D2)


Drag the code down through all of the parts of column G that you'll be using.


Code for H2:


=IF(G2=0, "", F2)


Drag the code down through all of the parts of H that you need it.


Hopefully that's what you need, or will at least get you closer to what you need.

Can you modify this for overnight (6pm-6am) using military time with decimals (11:45pm=23.75)?