**Simplified: Excel Work Week Hours sheet Question. Need formula help please!

[tke490]

Ok, this is a challenge! for simplicity I'll list only pertinent data needed. Say I have 7 rows (1 row for each day of wk and 8 columns in a time sheet: G-total hrs worked (that day), H-billable hours(of that days total), I-Shop hours (non-billable hrs worked of that days total), J-Running total hours(total cumulative billable + shop hours up to that day of the week), K-Running Billable hrs(tot. cumulative billable only hours up to that day), L-Running billable O.T.(total billable hrs that are now OT once the cumulative total billable +shop hours reach 40), M-Running Shop hrs(total shop only hours up to that day), N-Running Shop OT(total shop hrs that are now OT once cumulative total billable + shop reach 40).
Question: What equations would I use in columns K(running billable hrs), L(running billable OT), M(Running shop hrs), and N(running shop OT) to calculate these values for each day? Keep in mind that billable and shop hrs are cumulative and added together; as such will effect the point at which both billable and shop hrs transition into billable OT and shop OT for the running totals. I also need to account for the fact that the point where billable hrs or shop hrs turn into OT, may not be an even distribution of those hours and the remainder of whichever type of that days hrs where OT is reached need to carry over to the corresponding OT column. For example: J4"current running total hrs(meaning cumulative billable + cumulative shop) hrs""end of Thursday" might be 36.5 hrs. Then on Friday they work 15.5 billable and 2.5 shop hours. Since the first 3.5 billable hrs on Friday complete 40 cumulative J15"Running Total Hours""Friday", I need those 3.5 hrs only to add to K15"Running Billable Hrs""Friday", the remaining 12 billable hrs to show in L15"Running Billable OT""Friday", no change from previous days total in M15"Running Shop OT""Friday", and the 2.5 shop hrs worked on Friday that are now OT to ad in N15"Running Shop OT""Friday". Values for billable and shop from that point on should naturally carry over to the corresponding L-"running billable OT" or N-"running shop OT" column and rows for the remainder of the week. I can't use a formula like K15=IF(J14+H15<=40,SUM($H$11:$H15),40) for "Running Billable Hrs""Friday" because up till thurs., out of the total accumulated hours, only 19.5 of those might have been billable hrs specifically, and since I'm trying to total specifically only billable non-OT hrs in this particular column/row(k15), the formula wont work as it proves false on Fri. where we reach OT after 3.5 hrs and puts 40 in K15"Running Billable hrs" instead of the 3.5 hrs adding to the previous days total of 19.5 to place 23 in K15. Similarly I can't use an equation like L15=IF(J14+H15>40,J14+H15-40,0) to figure "Running Billable OT" since the value in the previous column/row "Running Billable Hrs" may not be greater than 40 even though that days billable hrs are OT because the remainder of non-OT hrs are shop hours that count towards 40 hours. See my delima? Is there a way of doing this to accurately reflect "running billable hrs","running billable OT","running shop hrs", "running shop OT"? Or- if easier, rather than showing these values day to day, is there a way of just showing the totals of these values for the week where it accurately adjusts for the shift from reg billable & shop to O.T. billable & O.T. shop keeping in mind billable and shop are cumulatively added together?

		G	H	I	J	K	L	M	N
		Total Hrs Worked	Billable Hrs.	Shop Hrs	Running Tot. Hrs.	Running Billable Hrs	Running Billable OT	Running Shop Hrs.	Running Shop OT
11	Mon.	9	1	8	9	1	0	8	0
12	Tues.	10	8	2	19	?formula	?formula	?formula	?Formula
13	Wed.	9	4	5	28	?	?	?	?
14	Thurs.	8.5	6.5	2	36.5	?	?	?	?
15	Fri.	18	15.5	2.5	54.5	?	?	?	?
16	Sat.	15.5	13.5	2	70	?	?	?	?
17	Sun.	15.5	13.5	2	85.5	?	?	?	?

<tbody>
</tbody>

 

[RickXL]

Hi,

Is this close to what you were expecting:

Excel 2013
	F	G	H	I	J	K	L	M	N
10		Total Hrs Worked	Billable Hrs.	Shop Hrs	Running Tot. Hrs.	Running Billable Hrs	Running Billable OT	Running Shop Hrs.	Running Shop OT
11	Mon.	9	1	8	9	1	0	8	0
12	Tues.	10	8	2	19	9	0	10	0
13	Wed.	9	4	5	28	13	0	15	0
14	Thurs.	8.5	6.5	2	36.5	19.5	0	17	0
15	Fri.	18	15.5	2.5	54.5	23	12	17	2.5
16	Sat.	15.5	13.5	2	70	23	25.5	17	4.5
17	Sun.	15.5	13.5	2	85.5	23	39	17	6.5
Sheet1

Cell Formulas
Range	Formula
K12	=MAX(MIN(40-J11,H12),0)+K11
K13	=MAX(MIN(40-J12,H13),0)+K12
K14	=MAX(MIN(40-J13,H14),0)+K13
K15	=MAX(MIN(40-J14,H15),0)+K14
K16	=MAX(MIN(40-J15,H16),0)+K15
K17	=MAX(MIN(40-J16,H17),0)+K16
L12	=H12-MAX(MIN(40-J11,H12),0)+L11
L13	=H13-MAX(MIN(40-J12,H13),0)+L12
L14	=H14-MAX(MIN(40-J13,H14),0)+L13
L15	=H15-MAX(MIN(40-J14,H15),0)+L14
L16	=H16-MAX(MIN(40-J15,H16),0)+L15
L17	=H17-MAX(MIN(40-J16,H17),0)+L16
M12	=MAX(MIN(40-J11-H12,I12),0)+M11
M13	=MAX(MIN(40-J12-H13,I13),0)+M12
M14	=MAX(MIN(40-J13-H14,I14),0)+M13
M15	=MAX(MIN(40-J14-H15,I15),0)+M14
M16	=MAX(MIN(40-J15-H16,I16),0)+M15
M17	=MAX(MIN(40-J16-H17,I17),0)+M16
N12	=I12-MAX(MIN(40-J11-H12,I12),0)+N11
N13	=I13-MAX(MIN(40-J12-H13,I13),0)+N12
N14	=I14-MAX(MIN(40-J13-H14,I14),0)+N13
N15	=I15-MAX(MIN(40-J14-H15,I15),0)+N14
N16	=I16-MAX(MIN(40-J15-H16,I16),0)+N15
N17	=I17-MAX(MIN(40-J16-H17,I17),0)+N16

Regards,

 

[tke490]

?!?!!! ARE YOU A WIZARD!???? Yes; that's perfect! Exactly what I'm looking for and works beautifully! Thank you so much, your amazing. I knew I was trying all the wrong logic formulas.... you just did in like probably less than an hour what has literally kept me up at night for two weeks. I can't stand a puzzle I can't solve but last night I determined that this was certainly a puzzle for a mind sharper than my own, Thank you again!!!

 

[RickXL]

?!?!!! ARE YOU A WIZARD!????

No, but I did sign a pact with the devil when I first discovered computers and programming.

I am pleased it is what you wanted and thanks for the feedback - much appreciated.

Regards,