Billing Calculator

[dpayne]

I've given myself a headache on how to do this. I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate. So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined. Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be. If someone has any idea how to do this (because I'm no programmer) the help would be greatly appreciated! danielfpayne@yahoo.com

 

[venkat1926]

I have given a sheet for equipment 1 ony . see the formula in F2 which is copied down.
similar caclualtion sheets have to be prepared for other equipment. NOTE THE DOLLAR SIGNS AT THE APPROPIRATE PLACES ).

Book1
	A	B	C	D	E	F
1	equipemnt1	datyrate	weeklyrate	monthlhlyrate	no.ofdaysrented	billamount
2	renting1	30	90	270	4	90
3	renting2				10	270
4	renting3				2	60
Sheet1

 

[DonkeyOte]

Welcome to the Board!

If I've understood you correctly how about something like:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Results					Lookup Table
2	Product	Hired	Returned	Cost		Product	Daily	Weekly	Monthly
3	A	10-Aug	13-Aug	90.00		A	30	90	270
4	A	10-Aug	19-Aug	180.00		B	40	180	650
5	A	10-Aug	11-Aug	60.00		C	50	220	800
6	B	10-Jul	10-Aug	690.00
7
8
Sheet1

Where:

-- F:I is your LOOKUP table of products and rates
-- A:C is where you would enter hire info
-- D is where cost is calculated based on dates and product hired.

Notes:

-- I have deemed start of hire and end of hire as being inclusive in the rates (ie hired 10th returned 11th equates to 2 days hire
-- I have deemed a month as being a calendar month as opposed to 28 days

If this approach is one you want to follow and either of the above is incorrect let us know.

 

[dpayne]

Thanks for both of your help!

lasw10, your formula was closer to what I'm looking for. Now I'm trying to see why it does not stick to the rate schedule. For example 8/1/08 - 8/31/08 rental period. On days 8-22 thru 8-27 it adds daily rates on top of the monthly then goes back to the monthly rate on 8-28 then on 8-29 & 8-30 it adds more daily rates. Then on 8-31 it goes back to the monthly rate. Any reason why this might happen?

Also, is there any way to specify this as a 28 day month? This simplifies the billing scedule.

 

[DonkeyOte]

Re: your August scenario -- not sure I follow, if I set B3 = 1st Aug and C3 = 31 Aug (Prod A) I get 270 (the monthly charge)

 

[dpayne]

Now try putting 8-1-08 to 8-22-8. See what happens?

 

[DonkeyOte]

Gotcha... let me have a think...

 

[dpayne]

On each of the dates listed in my previous post it will do that. These questions are definately out of my knowledge base, that's why ask you folks (the pros). I greatly appreciate all your assistance!

 

[DonkeyOte]

Below is a quick fix re: > month rate (does not resolve 28 days) and relates to D3 (can be copied down)

=MIN(DATEDIF($B3-1,$C3,"m")*VLOOKUP($A3,$F$2:$I$5,4,0)+MIN(VLOOKUP($A3,$F$2:$I$5,4,0),(INT(DATEDIF($B3-1,$C3,"md")/7))*VLOOKUP($A3,$F$2:$I$5,3,0))+MIN(VLOOKUP($A3,$F$2:$I$5,3,0),(MOD(DATEDIF($B3-1,$C3,"md"),7)*VLOOKUP($A3,$F$2:$I$5,2,0))),(DATEDIF($B3,$C3,"m")+1)*VLOOKUP($A3,$F$2:$I$5,4,0))

I am still sure this is not the best solution and no doubt won't resolve all issues so I will look into in more detail and post back.

 

[DonkeyOte]

Does this work for you ?

Again this is for D3 (can be copied down)

=MIN((DATEDIF($B3-1,$C3,"m")+1)*VLOOKUP($A3,$F$2:$I$5,4,0),(INT(DATEDIF($B3-1,$C3,"d")/28)*VLOOKUP($A3,$F$2:$I$5,4,0))+MIN(VLOOKUP($A3,$F$2:$I$5,4,0),INT(MOD(DATEDIF($B3-1,$C3,"d"),28)/7)*VLOOKUP($A3,$F$2:$I$5,3,0))+MIN(VLOOKUP($A3,$F$2:$I$5,3,0),MOD(DATEDIF($B3-1,$C3,"d"),7)*VLOOKUP($A3,$F$2:$I$5,2,0)))

If not post back with some dates that I can test to replicate.