I have a time reports table that could have multiple time reports for any 1 person. Each record contains hours for individual fields Sun-Saturday. These hours could be Regular or Benefit hours. If an employee is Hourly, and they work more than 40 Regular hours across their 1 or more Time Report Records, I need the OT calculation formula to evaluate how many OT hours should be applied to the Added/Updated record(s). I have 2 pipelines built to handle this: one for record(s) added and another for record(s) updated. The pipeline copies the OT calculation into the O/T Hours field for each record as applicable. The OT calculation below works for when they are adding hours above what is already there, but if they REDUCE hours (making a correction), it does not back out the O/T Hours and I'm not sure what adjustment I need to make to the formula for this to work. Any suggestions?
If([Task - Consultant - Salaried/Hourly]="Hourly" and [Consultant Total Reg Hours Current week]>40,
Min([Reg Hours],
[Consultant Total Reg Hours Current week]-40)+[O/T Hours],0)
