I need to calculate overtime (hours over 40) by project and I think I need a running total and a loop to do it. The data is only given to me by person by daily hours by project. I need to split it into straight time (<=40) and overtime (>40). I need it split that way before I can calculate total costs (ST hours * ST Rate) + (OT hours * OT Rate).
I have attached a copy of my spreadsheet. Subtotals don't really help because the calculations need to happen as the loop continues.
For Employee A, I need to keep a running total of their hours. When their hours become greater than 40, I need to start populating the overtime column.
Since I'm better at showing than telling, I have put a visual example of what I'm looking for in my attachment.