Fixed minimum rate for prorating calculation

[Lil Stinker]

Hello-
I'm looking for a way to fix in a minimum rate when calculating for monthly or weekly rental terms. In the samples below, I have the same table indicating the result for a monthly rental and a weekly rental based on a drop down in column E. The idea is the user will select Months or Weeks based on the term they are choosing for the rental when placing an order. Each term however, has a minimum rental rate regardless of which is chosen. Weekly rentals have a minimum 7-day rental while Monthly has a minimum one month rental. If a weekly rental is selected but the duration is less than 7 days, it should still total whatever weekly rate is listed in the Unit Rate column rather than breaking it down by the day as it currently does. Same for when a monthly rental is selected on the form, if the duration is less than a month, it should still total the monthly rate listed in column D. It is only after the minimum is met, does the rental start to prorate.

The two tables I have off to the right are there for calculating the proration for either rental term. I have it set up this way because this seems to produce the most accurate proration of rental rates for our needs. The problem is, it does not factor in a minimum rental period. So, I don't know if a more detailed IF statement in the Item Subtotal column is the way to go or if different formulas are needed in each of the calculation tables to factor in the minimum rental rate but I do know that I'm not smart enough to figure it out.

Any help would be greatly appreciated.

(By the way, this is meant to be used for drafting quotes to our clients. The start and end dates, equipment and rental rates are not fixed and will vary per quote).

ORDER FORM -formula tests- 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
17		Description	QTY	Unit Rate	Months	Item Subtotal		[ Term ]	Months	Weeks	Days	Weekly Rate	Daily Rate	Subtotal		Weeks	Days	Daily Rate	Subtotal
18		Computer Monitor	1	200.00	3w,6d	$192.84		Months	0.00	3.00	6.00	50.00	7.14	192.84		3.00	6.00	28.57	771.42
19		Speaker Set	2	50.00	3w,6d	$96.48		Weeks	0.00	3.00	6.00	12.50	1.79	96.48		3.00	6.00	7.14	385.68
20	Start Date	Keyboard & Mouse Set	1	20.00	3w,6d	$19.26			0.00	3.00	6.00	5.00	0.71	19.26		3.00	6.00	2.86	77.16
21	03/01/22	Computer Tower	1	500.00	3w,6d	$482.16			0.00	3.00	6.00	125.00	17.86	482.16		3.00	6.00	71.43	1928.58
22	End Date
23	03/28/22
24
25
26
27					Subtotal	$790.74
28					Discount	$0.00
29					Net Amount	$790.74
30					TOTAL AMOUNT	$790.74
31
32		Description	QTY	Unit Rate	Weeks	Item Subtotal		[ Term ]	Months	Weeks	Days	Weekly Rate	Daily Rate	Subtotal		Weeks	Days	Daily Rate	Subtotal
33		Computer Monitor	1	200.00	6d	$171.42		Months	0.00	0.00	6.00	50.00	7.14	42.84		0.00	6.00	28.57	171.42
34		Speaker Set	2	50.00	6d	$85.68		Weeks	0.00	0.00	6.00	12.50	1.79	21.48		0.00	6.00	7.14	85.68
35	Start Date	Keyboard & Mouse Set	1	20.00	6d	$17.16			0.00	0.00	6.00	5.00	0.71	4.26		0.00	6.00	2.86	17.16
36	04/01/22	Computer Tower	1	500.00	6d	$428.58			0.00	0.00	6.00	125.00	17.86	107.16		0.00	6.00	71.43	428.58
37	End Date
38	04/07/22
39
40
41
42					Subtotal	$702.84
43					Discount	$0.00
44					Net Amount	$702.84
45					TOTAL AMOUNT	$702.84
Sheet 2

Cell Formulas
Range	Formula
I18:I26	I18	=IF(ISBLANK(C18),"",DATEDIF($A$21,$A$23,"M"))
J18:J26	J18	=IF(ISBLANK(C18),"",INT(($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")))/7))
K18:K26	K18	=IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))
L33:L41,L18:L26	L18	=IF(ISBLANK(C18),"",(ROUND(D18/4,2)))
M33:M41,M18:M26	M18	=IF(ISBLANK(C18),"",(ROUND(L18/7,2)))
N33:N41,N18:N26	N18	=IF(ISBLANK(C18),"",(D18*I18+L18*J18+M18*K18)*C18)
P18:P26	P18	=IF(ISBLANK(C18),"",INT((DATEDIF($A$21,$A$23,"d")/7)))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))
R33:R41,R18:R26	R18	=IF(ISBLANK(C18),"",(ROUND(D18/7,2)))
S33:S41,S18:S26	S18	=IF(ISBLANK(C18),"",(D18*P18+R18*Q18)*C18)
E18:E26	E18	=IF(OR($B18="",AND(E$17<>"Months",E$17<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$17="Months",DATEDIF($A$21,$A$23,"m")&"m,"&INT(($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")))/7)&"w,"&MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7)&"d", INT(($A$23-$A$21)/7)&"w,"&MOD($A$23-$A$21,7)&"d"),"0m,",""),"0w,",""),",0d",""))
F18:F26	F18	=IF(ISBLANK(C18),"",IF(E$17="Months",(D18*I18+L18*J18+M18*K18)*C18,IF(E$17="Weeks",((D18*P18+R18*Q18)*C18))))
F27	F27	=SUBTOTAL(109,Table2261013[Item Subtotal])
L42:L45,L27:L30	L27	=IF(ISBLANK(C22),"",(ROUND(K27/7,2)))
M42:M45,M27:M30	M27	=IF(ISBLANK(C22),"",(D22*H27+K27*I27+L27*J27)*C22)
F29,F44	F29	=(F27-(F27*F28))
F30,F45	F30	=F29
I33:I41	I33	=IF(ISBLANK(C33),"",DATEDIF($A$36,$A$38,"M"))
J33:J41	J33	=IF(ISBLANK(C33),"",INT(($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")))/7))
K33:K41	K33	=IF(ISBLANK(C33),"",MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7))
P33:P41	P33	=IF(ISBLANK(C33),"",INT((DATEDIF($A$36,$A$38,"d")/7)))
Q33:Q41	Q33	=IF(ISBLANK(C33),"",MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7))
E33:E41	E33	=IF(OR($B33="",AND(E$32<>"Months",E$32<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$32="Months",DATEDIF($A$36,$A$38,"m")&"m,"&INT(($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")))/7)&"w,"&MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7)&"d", INT(($A$38-$A$36)/7)&"w,"&MOD($A$38-$A$36,7)&"d"),"0m,",""),"0w,",""),",0d",""))
F33:F41	F33	=IF(ISBLANK(C33),"",IF(E$32="Months",(D33*I33+L33*J33+M33*K33)*C33,IF(E$32="Weeks",((D33*P33+R33*Q33)*C33))))
F42	F42	=SUBTOTAL(109,Table226101316[Item Subtotal])

Cells with Data Validation
Cell	Allow	Criteria
E17	List	=$H$1:$H$3
E32	List	=$H$1:$H$3

 

[KRice]

Could you clarify something, please? It appears that you manually input the column D Unit Rates (it's called a "rate", but the time component is not clear). Then on any given row, the Unit Rate is divided by 4 (for the Weekly Rate computation)...so this suggests the Unit Rate that was input is a Monthly Unit Rate. The Daily Rate computation then divides the Weekly Rate by 7 (no issues there). And the Subtotal computations also treat the "Unit Rate" as a monthly rate because it is multiplied directly by the number of months. But then there is another apparently superfluous computation of Daily Rate in column R that divides the column D "Unit Rate" by 7 (which now suggests that the unit rate is a weekly rate...and not a monthly rate). Is this an error, or can you explain the apparent discrepancy?

 

[Lil Stinker]

Could you clarify something, please? It appears that you manually input the column D Unit Rates (it's called a "rate", but the time component is not clear). Then on any given row, the Unit Rate is divided by 4 (for the Weekly Rate computation)...so this suggests the Unit Rate that was input is a Monthly Unit Rate. The Daily Rate computation then divides the Weekly Rate by 7 (no issues there). And the Subtotal computations also treat the "Unit Rate" as a monthly rate because it is multiplied directly by the number of months. But then there is another apparently superfluous computation of Daily Rate in column R that divides the column D "Unit Rate" by 7 (which now suggests that the unit rate is a weekly rate...and not a monthly rate). Is this an error, or can you explain the apparent discrepancy?

Correct, the Unit Rate is entered manually. I should've changed the Unit Rates in the Weekly example to avoid any confusion but I was trying to show an apples to apples comparison when you flip from a Monthly rental to a Weekly rental. Typically, a weekly Unit Rate would be less than a monthly Unit Rate. Column R is showing the Daily Rate as broken down for a Weekly rate calculation therefore divided by 7.

The two tables on the right are basically there to "show my work" calculating the proration for a monthly rental rate (columns I:N) and a weekly rental rate separately (columns P:S). They're mainly there to catch any errors in case any of these complicated DATEDIF/EDATE formulas produce an anomaly (such as in leap years).

I hope that makes sense.

 

[KRice]

Thanks. It sounds like the time component of the inputted Unit Rate is the one specified in the column E headings. So the top table Unit Rate is the cost per unit per month, and the bottom table Unit Rate is the cost per unit per week?

 

[Lil Stinker]

Thanks. It sounds like the time component of the inputted Unit Rate is the one specified in the column E headings. So the top table Unit Rate is the cost per unit per month, and the bottom table Unit Rate is the cost per unit per week?

Yes, exactly!

 

[KRice]

Have a look at this to see if it does what you want. This version includes conditionals that zero out months when the Terms are Weeks---this let's you use the same table for both Terms types by making just a few accommodations in the formulas. The rental period is also reported in the same format that you originally had. Formulas have been tightened up somewhat for consistency and efficiency. Here, cell E17 uses Data Validation so the user must select either Weeks or Months (and then the logical checks adjust the formulas to make a single helper table accommodate either option). I've incorporated a MAX function, which allows you to enforce a minimum charge for either of the Terms. Let me know if you have any questions or encounter issues. I haven't extensively tested this, but it appears to work for the few cases I tried. You may have to adjust the formulas for the number of m/d/y by a day in some cases depending on whether the beginning and/or end dates are to be counted, which depends on the convention you use...check those carefully.

mrexcel_20220406.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
17	Date Range	Description	QTY	Unit Rate	Weeks	Item Subtotal			Terms: Weeks	Num Months	Num Weeks	Num Days	Monthly Rate	Weekly Rate	Daily Rate	Subtotal	Rental Period
18		Computer Monitor	1	200	5w,3d	1085.71			Weeks	0	5	3		200	28.57	1085.71	0m,5w,3d
19		Speaker Set	2	50	5w,3d	542.84			Weeks	0	5	3		50	7.14	542.84	0m,5w,3d
20	Start Date	Keyboard & Mouse Set	1	20	5w,3d	108.58			Weeks	0	5	3		20	2.86	108.58	0m,5w,3d
21	4/1/22	Computer Tower	1	500	5w,3d	2714.29			Weeks	0	5	3		500	71.43	2714.29	0m,5w,3d
22	End Date
23	5/9/22
24
25
26
27					Subtotal	4451.42
28					Discount	10.0%
29					Net Amount	4006.28
30					TOTAL AMOUNT	4006.28
Sheet2

Cell Formulas
Range	Formula
I17	I17	="Terms: "&$E17
I18:I26	I18	=IF(ISBLANK(C18),"",SUBSTITUTE(I$17,"Terms: ",""))
J18:J26	J18	=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23,"M"),0))
K18:K26	K18	=IF(ISBLANK(C18),"",QUOTIENT(DATEDIF(EDATE($A$21,J18),$A$23,"D"),7))
L18:L26	L18	=IF(ISBLANK(C18),"",MOD(DATEDIF($A$21,$A$23,"D"),7))
M18:M26	M18	=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
N18:N26	N18	=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),D18))
O18:O26	O18	=IF(ISBLANK(C18),"",(ROUND(N18/7,2)))
P18:P26	P18	=IF(ISBLANK(C18),"",C18*MAX(IF(I18="Months",M18,N18),SUMPRODUCT(J18:L18,M18:O18)))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",J18&"m,"&K18&"w,"&L18&"d")
E18:E26	E18	=IF($B18="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Q18,"0m,",""),"0w,",""),",0d",""))
F18:F26	F18	=P18
F27	F27	=SUBTOTAL(109,F18:F26)
F29	F29	=F27*(1-F28)
F30	F30	=F29

Cells with Data Validation
Cell	Allow	Criteria
E17	List	Months,Weeks

 

[Lil Stinker]

Thanks for your feedback! I will take a look at this and try it out over the next few days and follow up with any questions.

Thanks again

 

[Lil Stinker]

Hi Kirk-
Your sample layout is great, the simplicity is awesome! I'm still putting it through its paces but I've got one issue so far and one additional request.

Issue: I am getting wildly inconsistent results in the Number of Days, column L. For the most part, it counts the end day as part of the rental, which is actually preferred from a rental perspective (even if you return in the morning, you're still paying for the day). However, other times it's adding a couple days or even more. For instance, if I plug in Start: 04/12/22 and End: 06/11/22, I get back 1m, 4w, 4d with your formula when it's really 1m, 4w, 2d. Not sure where it's getting 2 extra days from. So I went back to the original day calculation formula I was using which matches the results I get from the Microsoft Date Calculator. Only problem with that formula is that it doesn't include the end date as a billed day. Is there a way to get the original formula to include the end day as opposed to just counting up to it? Original formula: =IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))

Request: Is there a way to have the monthly calculation round up to a whole month any time the rental duration reaches 1 month and 4 weeks? Like in the example above, 1 month, 4 weeks and 2 days would just equal two months. Because, as it turns out, if we charge a rate of $200/mo and the client has the equipment out for 1m, 4w, 2d, it actually ends up costing more at 4w, 2d then it would for an even 2 month rental.

Thanks again for your help!

 

[KRice]

I see the issue with "days". The current worksheet uses

MOD(DATEDIF($A$21,$A$23,"D"),7)

...and this will simply count the number of days between a start and end date. We perform a MOD 7 on that to get the number of days after accounting for weeks. But what if the date range spans months with 29, 30, 31 days? The formulas for months and weeks use: 1) DATEDIF with "M" (which counts number of months based on keeping the day "number" constant...so Apr 12 to May 12 to Jun 12 is two months); and 2) EDATE (which counts forward so many months). Since April has 30 days and May 31, these formulas that count months between dates or jump forward so many months don't care how many days are actually involved. The current "days" formula, however, is looking at leftover days after accounting for jumping through the date range a week at a time (7 days at a time). And since a month is rarely 28 days, the current formula isn't really in sync with with how the months and weeks are determined.

To fix this, it makes sense to revise the days formula to maintain the same convention used for months and weeks. We use the same basic formula found in the weeks formula, which returns days (where the number of days is operated on by taking the integer after dividing by 7....or QUOTIENT 7...to give number of weeks). Now we use the same formula for the number of days and subtract 7*(number of weeks) to give the leftover number of days.

DATEDIF(EDATE($A$21,J18),$A$23,"D")-7*K18

Tell me about counting the end date, as this may have implications for months, weeks, and days. If the start date is 04/12/22 and end date is 05/12/22, is that 1 month?... or 1 month and 1 day? The current formulas treat it as exactly one month, but 5/12/22 really represents the beginning of the 2nd month. If this makes sense to you and is consistent with your cost estimation method, then I think the formulas should be revised to include a "+1" where the end date appears (once in each of the formulas for months, weeks, and days).

The following sample includes all of the changes mentioned above.

mrexcel_20220406.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
17	Date Range	Description	QTY	Unit Rate	Months	Item Subtotal			Terms: Months	Num Months	Num Weeks	Num Days	Monthly Rate	Weekly Rate	Daily Rate	Subtotal	Rental Period
18		Computer Monitor	1	200	1m,4w,2d	414.28			Months	1	4	2	200	50	7.14	414.28	1m,4w,2d
19		Speaker Set	2	50	1m,4w,2d	207.16			Months	1	4	2	50	12.5	1.79	207.16	1m,4w,2d
20	Start Date	Keyboard & Mouse Set	1	20	1m,4w,2d	41.42			Months	1	4	2	20	5	0.71	41.42	1m,4w,2d
21	4/12/22	Computer Tower	1	500	1m,4w,2d	1035.72			Months	1	4	2	500	125	17.86	1035.72	1m,4w,2d
22	End Date
23	6/10/22
24
25
26
27					Subtotal	1698.58
28					Discount	10.0%
29					Net Amount	1528.72
30					TOTAL AMOUNT	1528.72
Lil Stinker

Cell Formulas
Range	Formula
I17	I17	="Terms: "&$E17
I18:I26	I18	=IF(ISBLANK(C18),"",SUBSTITUTE(I$17,"Terms: ",""))
J18:J26	J18	=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18:K26	K18	=IF(ISBLANK(C18),"",QUOTIENT(DATEDIF(EDATE($A$21,J18),$A$23+1,"D"),7))
L18:L26	L18	=IF(ISBLANK(C18),"",DATEDIF(EDATE($A$21,J18),$A$23+1,"D")-7*K18)
M18:M26	M18	=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
N18:N26	N18	=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),D18))
O18:O26	O18	=IF(ISBLANK(C18),"",(ROUND(N18/7,2)))
P18:P26	P18	=IF(ISBLANK(C18),"",C18*MAX(IF(I18="Months",M18,N18),SUMPRODUCT(J18:L18,M18:O18)))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",J18&"m,"&K18&"w,"&L18&"d")
E18:E26	E18	=IF($B18="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Q18,"0m,",""),"0w,",""),",0d",""))
F18:F26	F18	=P18
F27	F27	=SUBTOTAL(109,F18:F26)
F29	F29	=F27*(1-F28)
F30	F30	=F29

Cells with Data Validation
Cell	Allow	Criteria
E17	List	Months,Weeks

As for the rounding question, I'm thinking about that. As the worksheet is currently set up, we would get a circular reference error because some decision on rounding months depends on the number of weeks, yet we use the number of months to determine the number of weeks. This suggests either a different approach is needed, or perhaps simpler, several more helper columns would be needed. Those helper columns would first perform the current calculations for months, weeks, and days. Then some logic would examine the number of weeks, and if W=4, then make M=M+1, W=0 and D=0. And if W<>4, then leave M, W, and D as they are. Then the cost calculations and Rental Period text string would refer to this conditional section for M/W/D rather than the original section for M/W/D. Are you okay with another 3 or 4 helper columns?

 

[KRice]

Here is a version I described above: rounding using three more helper columns...

mrexcel_20220406.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
16										Original			Conditional
17	Date Range	Description	QTY	Unit Rate	Months	Item Subtotal			Terms: Months	Num Months	Num Weeks	Num Days	Num Months	Num Weeks	Num Days	Monthly Rate	Weekly Rate	Daily Rate	Subtotal	Rental Period
18		Computer Monitor	1	200	2m,1d	407.14			Months	2	0	1	2	0	1	200	50	7.14	407.14	2m,0w,1d
19		Speaker Set	2	50	2m,1d	203.58			Months	2	0	1	2	0	1	50	12.5	1.79	203.58	2m,0w,1d
20	Start Date	Keyboard & Mouse Set	1	20	2m,1d	40.71			Months	2	0	1	2	0	1	20	5	0.71	40.71	2m,0w,1d
21	4/12/22	Computer Tower	1	500	2m,1d	1017.86			Months	2	0	1	2	0	1	500	125	17.86	1017.86	2m,0w,1d
22	End Date
23	6/12/22
24
25
26
27					Subtotal	1669.29
28					Discount	10.0%
29					Net Amount	1502.36
30					TOTAL AMOUNT	1502.36
Lil Stinker

Cell Formulas
Range	Formula
I17	I17	="Terms: "&$E17
I18:I26	I18	=IF(ISBLANK(C18),"",SUBSTITUTE(I$17,"Terms: ",""))
J18:J26	J18	=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18:K26	K18	=IF(ISBLANK(C18),"",QUOTIENT(DATEDIF(EDATE($A$21,J18),$A$23+1,"D"),7))
L18:L26	L18	=IF(ISBLANK(C18),"",DATEDIF(EDATE($A$21,J18),$A$23+1,"D")-7*K18)
M18:M26	M18	=IF(ISBLANK(C18),"",IF(K18>=4,J18+1,J18))
N18:N26	N18	=IF(ISBLANK(C18),"",IF(K18>=4,0,K18))
O18:O26	O18	=IF(ISBLANK(C18),"",IF(K18>=4,0,L18))
P18:P26	P18	=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),D18))
R18:R26	R18	=IF(ISBLANK(C18),"",(ROUND(Q18/7,2)))
S18:S26	S18	=IF(ISBLANK(C18),"",C18*MAX(IF(I18="Months",P18,Q18),SUMPRODUCT(M18:O18,P18:R18)))
T18:T26	T18	=IF(ISBLANK(C18),"",M18&"m,"&N18&"w,"&O18&"d")
E18:E26	E18	=IF($B18="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T18,"0m,",""),"0w,",""),",0d",""))
F18:F26	F18	=S18
F27	F27	=SUBTOTAL(109,F18:F26)
F29	F29	=F27*(1-F28)
F30	F30	=F29

Cells with Data Validation
Cell	Allow	Criteria
E17	List	Months,Weeks