Maximum billable hours

talkwald

Board Regular
Joined
Oct 6, 2003
Messages
67
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32
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

Board Regular
Joined
Oct 6, 2003
Messages
67
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
 

Forum statistics

Threads
1,136,260
Messages
5,674,693
Members
419,520
Latest member
Jennifer4Dillon

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top