Issues with Shift Differential/OT Formulas (table included)

[azepeda88]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name[/TD]
[TD]Start time[/TD]
[TD]End Time[/TD]
[TD]Actual Hrs[/TD]
[TD]Straight[/TD]
[TD]Shift Diff[/TD]
[TD]OT[/TD]
[TD]Shift Diff OT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]02/24/2018 06:10[/TD]
[TD]02/24/18 18:40[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD].7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]02/25/2018 06:10[/TD]
[TD]02/25/18 18:40[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD].7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]03/02/2018 06:10[/TD]
[TD]03/02/18 18:40[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD].7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]03/03/2018 06:10[/TD]
[TD]03/03/18 18:40[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD].7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]40[/TD]
[TD]2.8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)?