Excel Work Hours Sheet: need help with Cumulative Totals Formulas

[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 ? ? ? ?

[amit.wilson]

Dude, that's a serious looking paragraph. :-)

Perhaps if you made a sample excel, simplified the "ask" (or broke it down and asked just for the 1st few bits) then someone (me?) could work on getting this sorted for you?

Just saying. :-)

Cheers

[tke490]

Dude, that's a serious looking paragraph. :-)

Perhaps if you made a sample excel, simplified the "ask" (or broke it down and asked just for the 1st few bits) then someone (me?) could work on getting this sorted for you?

Just saying. :-)

Cheers

Thank you for the reply. I wrote the explanation detailed because I couldn't upload a sample sheet for visual reference. Then I attempted to recreate the sheet after the paragraph and it looked fine when submitting, but looking at the way it ended up formatting after submitting now, it's useless. The paragraph really isn't that scary if you just create or sketch out a simple 7 row, 8 col table and label as described. Paragraph after that walks you through real easy after that and you'll immediately see my question with last 4 column formulas.

[amit.wilson]

So to upload a sample sheet, try this: Click on "Go Advanced". Look for a link "Manage Attachment". Then "Choose a file", then "Upload", and then "Close This WIndow" and then "Submit Reply".

And this is the Attachment Forum FAQ: http://www.excelforum.com/faq.php?do...l&titlesonly=0

Cheers