I'm putting together a timesheet for my company and I'm having a bit of trouble doing hour totaling. On the same sheet I have rows for different job numbers and columns for days of the week. I also have 2 sections on one sheet, one relating to regular time worked and one relating to hours worked with a shift premium. On top of that I also have different overtime tiers, of 1.0X, 1.5X, and 2.0X if the employee works 40, 40-60, and 60+ hours respectively. Also If an employee works more than 12 hours in a day the hours above 12 are also 1.5X.

On top of all that I'm trying to set it up to take the 1st 40 hours worked as straight time whether they are regular hours or hours worked at a premium, the next 20 at 1.5X, and anything above at 2.0X, while still tracking whether they are regular or premium hours, with over 12 hours worked in a day going directly to 1.5X Overtime on regular or premium hours.

I currently set up to count the hours of each type (regular or premium) by day and week, and split them into the overtime tiers based on total hours worked but I'm at a loss on how to add the other rules.

I know this is difficult to describe with words so i'm sorry for that, but hopefully it still makes sense. I'm fairly new to excel so any help would be greatly appreciated.

Thanks