excel spreadsheet 4 diff pay scales challenge!

[tke490]

Here's a challenge for you! We have 4 different pay scales at work (billable, non-billable, billable o.t., and non-billable o.t.). I have a 14 day "daily hours sheet" in excel and need to have running tally of these 4 values per day inclusive of when they swap over into overtime hours. E.g. an employee might work 7 billable hours and have 3 non-billable hours on monday, tuesday 2 billable hours and 9 non-billable hours, etc. once 40 hours is reached of these 2 combined values, they go into the next two pay scales (billable o.t., non-billable o.t.). I need a formula to show these 4 values and reflect accurately when they swap over into the two overtime values. The way I presently have it is wrong and tallys the billable hours up to 40 hours (of the week total) and counts everything after that whether billable or not as billable overtime and same for non billable hours. With the combination of "base" billable and non billable hours, a person might reach their 40 hours on say Thursday, and I don't have a way of adjusting hours for the remainder of the week whether billable or non billable into the respective overtime scale for those hours(billable o.t. or non-billable). I humbly request the help of minds greater than mine.
- The table I have set up reading from left to right clmn values are as follows: Date, Time in, Lunch start, lunch end, time out, total hours worked, billable hours, non-billable hours. The total hours worked auto populates that days value less time for lunch with the equation I have based on punch in punch out values, but I have to manually fill in how many of those total hours were billbalble and non-billable. on the bottom of the table i have a =sum total that tells me the weeks total hours worked, total billable, and total non billable but I dont have a way of distinguishing O.t. hours.
-Both billable and nonbillable hours being cumlative, a person may for example, have 30 billable hours and 9 nonbillable hours by 3pm wednesday then at 4pm wednesday they happen to be doing non billable work for an additional 2 hours (now nonbillable O.t.). Thursday they clock in another 8 billable hours (now in o.t.) and 2 nonbillable O.t. hours and so on for the rest of the week. I need 4 columns that show the cumlative total each day throughout the week showing "total billable hrs x pay", "total nonbillable hours x pay", "total billable o.t. hrs x pay", "total nonbillable ot hrs note: x pay" (x=*)
- Is there an equation that shows: for the week there were (say) 30 billable pay rate hours, 10 non- billable pay rate hours, 42 billable O.T. rate hours and 7 non-billable O.T. rate hours? That make more sense of what I'm trying to achieve haha? I know its kind of hard to explain in one swoop above. But thats my end goal- to find out those 4 total values for the week, multiply them by their respective pay grades, and have it auto transfer billable and non billable hours totals to the repective O.t. pay grade columns for the remainder of that week once 40 hours has been reached.
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total Hours Worked
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Billable Hrs
[/TD]
[TD]Total Non-Billable Hrs
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=Sum(F8,G8)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]=sum(H11:H24)
[/TD]
[TD]=sum(I11:I24)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Date
[/TD]
[TD]Time In
[/TD]
[TD]Lunch start
[/TD]
[TD]Lunch End
[/TD]
[TD]Time out
[/TD]
[TD]Total Hours
[/TD]
[TD]Billable Hours
[/TD]
[TD]Non-Billable hours
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]5/16
[/TD]
[TD]8:00
[/TD]
[TD]na
[/TD]
[TD]na
[/TD]
[TD]17:00
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]5/17
[/TD]
[TD]4:30
[/TD]
[TD]13:00
[/TD]
[TD]14:00
[/TD]
[TD]15:30
[/TD]
[TD]10
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]5/18
[/TD]
[TD]7:00
[/TD]
[TD]na
[/TD]
[TD]na
[/TD]
[TD]16:00
[/TD]
[TD]9
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]5/19
[/TD]
[TD]5:00
[/TD]
[TD]na
[/TD]
[TD]na
[/TD]
[TD]13:30
[/TD]
[TD]8.5
[/TD]
[TD]6.5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]5/20
[/TD]
[TD]5:30
[/TD]
[TD]na
[/TD]
[TD]na
[/TD]
[TD]23:30
[/TD]
[TD]18
[/TD]
[TD]15.5
[/TD]
[TD]2.5
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]5/21
[/TD]
[TD]5:30
[/TD]
[TD]na
[/TD]
[TD]na
[/TD]
[TD]21:00
[/TD]
[TD]15.5
[/TD]
[TD]13.5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]5/22
[/TD]
[TD]5:30
[/TD]
[TD]na
[/TD]
[TD]na
[/TD]
[TD]21:00
[/TD]
[TD]15.5
[/TD]
[TD]13.5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Reg. Billable Hours x pay
[/TD]
[TD]Reg. Non-Billable hours x pay
[/TD]
[TD]Billable O.T. x pay
[/TD]
[TD]Non-Billable O.T. x pay
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]????
[/TD]
[TD="align: center"]????
[/TD]
[TD="align: center"]????
[/TD]
[TD="align: center"]????
[/TD]
[/TR]
</tbody>[/TABLE]