I have created a time sheet based on a 10 hour day and I've successfully incorporated the overtime rates for 1.5x and 2x regular pay. We pay 1.5x for hours 11 and 12 and 2x for hour 13 and beyond.

I already have the sheet calculating the OT pay based on the hourly rate *1.5 or 2.

However, I still need to manually enter the number of OT hours in the Rate 1 column and the number of OT hours in the Rate 2 column.

Is there any way to get Excel to look at the total hours in Column A, and then understand that if that number is 15 for example, 2 hours need to go in Column B (Rate 1) and then 3 hours need to go in Column C (Rate 2)?

Thanks!