Overtime Calculation with Multiple Work Rates
I need a formula that will accomplish the following, and I am not sure how to create it. We have employees who have 2 different rates, one for Work and one for Drive. Our payroll spreadsheet has each day of the week broken into work and drive hours. We need a formula that will take the summed total for work and drive hours, apply the first 40 total hours to their respective column, and add the remaining work and drive hours to the work and drive overtime columns. I have figured out how to create a formula that will put the first 40 hours into Work and the remaining hours into Work-OT, but cannot determine how to accomplish this considering both rates, ie. if the employee hits 40 hours half way through their drive on Friday, the remaining drive hours as well as drive hours from Sat + Sun are put to the Drive-OT column, and the remaining work hours for Sat + Sun are put to Work-OT.