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

 

[dpayne]

This is doing close to the same as the other. After 28 days a new billing cycle should start. So after day 28 it should start adding charges on top of the previous month and so on.

 

[DonkeyOte]

Not sure I follow... using the latest formula in D I get the below using the following start/end dates -- see row 7.

Excel Workbook
	A	B	C	D
1	Results
2	Product	Hired	Returned	Cost
3	A	10-Aug	13-Aug	100.00
4	A	10-Aug	19-Aug	190.00
5	A	01-Aug	22-Aug	270.00
6	A	22-Aug	31-Aug	190.00
7	A	10-Jul	10-Aug	370.00
dpayne

I've used Product A for all rows to keep things simple and have used following rate table:

Excel Workbook
	F	G	H	I
1	Lookup Table
2	Product	Daily	Weekly	Monthly
3	A	30	100	270
4	B	40	180	650
5	C	50	220	800
dpayne

I changed Weekly from 90 to 100 such that 3 weeks would exceed the monthly rate for testing purposes (ie should override 300 with 270).

Below is a little breakdown of how things work (no formulas presented just results)

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T
1		Days				Cost
2	Days	28	7	1		28	7	1		Final
3	4	0	0	4		0	0	100		100
4	10	0	1	3		0	100	90		190
5	22	0	3	1		0	270	30		270
6	10	0	1	3		0	100	90		190
7	32	1	0	4		270	0	100		370
dpayne

 

[dpayne]

Try putting in these dates......... 8-1-08 thru 8-30-08. I get 270 for a month, rather than 270 + 60 = 330. Since the 1 month cycle ends at 28 days and there are 2 more days ($60)....that should make the total $330. Make sense? Our billing cycle is confusing at times.

 

[dpayne]

So lets say................. Date Out is 8-1-8 then the return date is 8-28-08. This is a once month bill since it is in the 28 days. If they return it on 8-29-08, that's one month and one day (270 + 30) at 300. Any better?

 

[DonkeyOte]

DP -- getting there...

Try (D3 onwards)

=MIN(CEILING(DATEDIF($B3-1,$C3,"d")/28,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)))

 

[dpayne]

My friend, I think you may have nailed it! I'll keep running some examples through it. I'll let you know!

Thanks a bunch!!