Maximum billable hours

[talkwald]

Hi - I'm trying to formulate the following rule "A maximum of up to ten (10) unused hrs at the end of one (1) month billing period will be rolled
over for use the following month. There will be no more than a one month accumulation of ten (10) hours rolled over at any time" into a worksheet. Essentially, our contract allows us to bill 32 hours per month but sometimes the techies work fewer hours. We still bill the base 32 hrs, bu we want to allow up to 10 hrs to roll into a given month. If the techies work over the 32 hours, we bill the customer for overage (after applying a maximum of up to 10 hrs of prior month credit).

In my sample, the "Calculated Billable Hours" should be 50 hrs (cell F32) because 60 hrs were worked with the max. credit of 10 hrs applied. Can anyone solve this for me.....??? Many thanks in advance.

Barbara

HCC 2005-06with max.xls
	A	B	C	D	E	F	G	H
28	Month	Contracted	ActualHrsWorked	HrsOver/UnderContract	Cumulative	Calc'dHrsBillable	rate	Extended
29	july	32	50.00	18.00	18.00	50.00	$35	$1,750.00
30	aug	32	10.00	-22.00	-22.00	32.00	$35	$1,120.00
31	sept	32	15.00	-17.00	-39.00	32.00	$35	$1,120.00
32	oct	32	60.00	28.00	-11.00	32.00	$35	$1,120.00
Billing Calcs

 

[venkat1926]

I have slightly changed your formulas and used "unused hours " as headingt in D1. if you want you keep the your own heading. Ihave added nov and dec with less than 10 hrs overtime in one case for testing.

see the formulas perhaps column F is not necessary. check thoroghly all the cells partiucalrly columns D to G.

venkat

the spredsheet is below

Book6
	A	B	C	D	E	F	G	H
1	Month	Contracted	ActualHrsWorked	unusedhours	Cumulative	Calc'dHrsBillable	rate	Extended
2	july	32	50	0	50	50	$35	$1,750
3	aug	32	10	22	32	32	$35	$1,120
4	sept	32	15	17	32	32	$35	$1,120
5	oct	32	60	0	60	60	$35	$2,100
6	nov	32	29	0	29	29	$35	$1,015
7	dec	32	15	17	32	32	$35	$1,120
Sheet1

 

[sunnyland]

Hello to you both,

I have a different way to handle the problem but my solution may be wrong as I was[sorry n't] sure I understood 100% the problem.

What I understood is you charge a 32 no matter what but if it is over 32 hours and the month before had a credit then will take off the credit from the hours over 32 but it can't be less than 32.

Here the solution to my understanding. I create a named range based on the first row to make understanding my solution easier.

path.xls
	A	B	C	D	E	F	G	H
1	Month	Contracted	Actual	Over32	Under32	billable	rate	Extended
2	july	$32.00	12	0	10	32.00	$35.00	$1,120.00
3	aug	$32.00	72	40	0	62.00	$35.00	$2,170.00
4	sept	$32.00	15	0	10	32.00	$35.00	$1,120.00
5	oct	$32.00	60	28	0	50.00	$35.00	$1,750.00
6	nov	$32.00	29	0	3	32.00	$36.00	$1,152.00
7	dec	$32.00	15	0	10	32.00	$37.00	$1,184.00
Sheet1

I think the hardest thing in problem solving is to get the problem right rather then finding a solution

 

[macajm]

My solution is to change your column E to be called "Credit Hours" and use the following formula in E29 to calculate the available credit hours (to be deducted from the Actual hours worked in F29)
E29=IF(D28<0,MAX(-10,D28),0)
noting that it refers to cell D28 in the previous row!
Then in F29 the Calc'd Hours Billable becomes F29=MAX(B29,B29+D29+E29).

 

[talkwald]

Thanks everyone -- Both sunnyland & macajm solutions work perfectly-- how to decide which one to use?? I'll keep both on hand.....Many many thanks for making me look smarter than I am!!

Barbara