# Maximum billable hours

#### talkwald

##### Board Regular
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
ABCDEFGH
28MonthContractedActualHrsWorkedHrsOver/UnderContractCumulativeCalc'dHrsBillablerateExtended
29july3250.0018.0018.0050.00\$35\$1,750.00
30aug3210.00-22.00-22.0032.00\$35\$1,120.00
31sept3215.00-17.00-39.0032.00\$35\$1,120.00
32oct3260.0028.00-11.0032.00\$35\$1,120.00
Billing Calcs

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
ABCDEFGH
1MonthContractedActualHrsWorkedunusedhoursCumulativeCalc'dHrsBillablerateExtended
2july325005050\$35\$1,750
3aug3210223232\$35\$1,120
4sept3215173232\$35\$1,120
5oct326006060\$35\$2,100
6nov322902929\$35\$1,015
7dec3215173232\$35\$1,120
Sheet1

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
ABCDEFGH
1MonthContractedActualOver32Under32billablerateExtended
2july\$32.001201032.00\$35.00\$1,120.00
3aug\$32.007240062.00\$35.00\$2,170.00
4sept\$32.001501032.00\$35.00\$1,120.00
5oct\$32.006028050.00\$35.00\$1,750.00
6nov\$32.00290332.00\$36.00\$1,152.00
7dec\$32.001501032.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

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).

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

Replies
7
Views
692
Replies
1
Views
257
Replies
1
Views
1K
Replies
10
Views
457
Replies
3
Views
2K

1,217,750
Messages
6,138,401
Members
450,134
Latest member
TYoung24

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back