Prorating formula based on weeks, months

[Lil Stinker]

The formula I have in the Subtotal column below doesn't quite work for what I need. What I'm looking for is when a user selects "Months" from the drop down in cell E1, I need the formula to begin prorating after a 30-day minimum period. I also need it to prorate when the user selects "Weeks", to start prorating after a 7-day minimum. This way, if they are taking equipment out for any period of time less than the required one month or one week minimum, the rate does not start prorating immediately.

What I currently have for the weekly IF formula sort of works but the math is off. As you can see in the example, $200*2*1.71 should equal $684 and $100*1.71 should equal $171 but somehow it is adding change from somewhere. The totals come out correct only when the period of time is an even number like 1 week or 2 weeks.

I'm pretty inept at putting together Excel formulas... please help!

ORDER FORM -formula tests- 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Description	QTY	Unit Rate	Weeks	Item Subtotal		[ Term ]
2	Pickup Date	MiFi Hotspot	2	200.00	1.71	$685.72		Months
3	12/29/22	MiFi Hotspot	1	100.00	1.71	$171.43		Weeks
4	Billing Start
5	02/24/22
6	Billing End							Months	Weeks	Days	Weekly Rate	Daily Rate	Subtotal
7	03/08/22							0.00	1.00	5.00	50.00	7.14	171.40
8	Return Date							0.00	1.00	5.00	25.00	3.57	42.85
9	04/01/22
10
11					Subtotal	$857.15
12					Discount	$0.00
13					Net Amount	$857.15
14					TOTAL AMOUNT	$857.15
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=IF(E$1="Weeks",ROUNDDOWN((DATEDIF($A$5,$A$7,"d")/7),2),IF(OR($B2="",AND(E$1<>"Months")),"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$1="Months",DATEDIF($A$5,$A$7,"m")&"m,"&INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7)&"w,"&MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7)&"d",INT(($A$7-$A$5)/7)&"w,"&MOD($A$7-$A$5,7)&"d"),"0m,",""),"0w,",""),",0d","")))
F2	F2	=IF(ISBLANK(C2),"",IF(E$1="Months",(D2*H7+K7*I7+L7*J7)*C2,IF(E$1="Weeks",ROUNDUP((MAX(D2,DATEDIF($A5,$A7,"d")*D2/7)*C2),2))))
F3	F3	=IF(ISBLANK(C3),"",IF(E$1="Months",(D3*H8+K8*I8+L8*J8)*C3,IF(E$1="Weeks",ROUNDUP((MAX(D3,DATEDIF($A5,$A7,"d")*D3/7)*C3),2))))
H7:H14	H7	=IF(ISBLANK(C2),"",DATEDIF($A$5,$A$7,"M"))
I7:I14	I7	=IF(ISBLANK(C2),"",INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7))
J7:J14	J7	=IF(ISBLANK(C2),"",MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7))
K7:K14	K7	=IF(ISBLANK(C2),"",(ROUND(D2/4,2)))
L7:L14	L7	=IF(ISBLANK(C2),"",(ROUND(K7/7,2)))
M7:M14	M7	=IF(ISBLANK(C2),"",(D2*H7+K7*I7+L7*J7)*C2)
F11	F11	=SUBTOTAL(109,Table22[Item Subtotal])
F13	F13	=(F11-(F11*F12))
F14	F14	=F13

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

 

[StephenCrump]

$200*2*1.71 should equal $684 and $100*1.71 should equal $171 but somehow it is adding change from somewhere.

Your formula in F2 is not using E2. It's calculating the period as 12 days/7 = 1.714 ... weeks , without rounding to 1.71 as the formula in E2 does.

 

[Lil Stinker]

Thanks, but I haven’t been able to get the formula from E2 in part or in full to work properly in F2. And, I still don’t know how to lock in the minimum rate for the monthly formula or the weekly formula and then start prorating only after the minimum.

The monthly formula is especially confusing because there are multiple formulas used in calculating the correct proration. The table in H-L is how I break out the monthly proration but how can I lock in the 30-day minimum when calculating the subtotal? And wrap it all up in one tidy formula that covers all the bases when you pick monthly or weekly from the drop down?

 

[StephenCrump]

It's not totally clear what you're trying to do, but it looks like you're over-complicating it ...

If you're charging by the month/week/day, it makes sense to split the hire period into months, weeks and days as you have done.

I can see you got your formula in E2 from this post: Help with Month, Week, Daily formula. The reason this formula is so complicated is that it duplicates the calculations that you're already doing separately in your months/weeks/days table. You can concatenate these intermediate results rather than re-calculating them.

It's only a guess, but I think you're saying that if someone chooses to hire:

- by the month, the minimum charge is one monthly fee, and thereafter the monthly fee for whole months, and the daily fee/30 for part months?
- by the week, the minimum charge is one weekly fee, and thereafter the weekly fee/7 for part weeks?

But that's not what your formula is doing, e.g. if someone hires on a monthly basis from 1 March to 30 March, your formula will charge for 4w, 1d. (And it also incorrectly charges 4.14 times the monthly fee, rather than the weekly fee!) Note that the solutions provided in the above post take your formula as a given starting point.

If my interpretation is correct, can you please provide a few examples along the following lines. We'll need to understand exactly how you want to calculate months, e.g. hire date 31 December 2021, return date 28 February 2022. Is this two whole months? Or 1m, 28d? Please also let us know what intermediate rounding you want to use, e.g. it looks like 1w, 1d should be taken as 1.14 weeks, i.e. rounded to 2dp. What about 1m, 1d? Is this 1.03 months?

	A	B	C	D	E	F	G
1	MonthlyRate	$200
2	WeeklyRate	$50
3
4	Hire	Return	Type	Months	Weeks	Days	Fee
5	1 Jan 2022	3 Jan 2022	Weekly		1		$50.00
6	1 Jan 2022	16 Jan 2022	Weekly		2	1	$107.14
7	1 Jan 2022	16 Jan 2022	Monthly	1			$200.00
8	1 Jan 2022	16 Feb 2022	Monthly	1		15	$300.00
9	etc?
Sheet1

Cell Formulas
Range	Formula
G5:G8	G5	=D5*MonthlyRate+E5*WeeklyRate+F5*IF(C5="Weekly",WeeklyRate/7,MonthlyRate/30)

Named Ranges
Name	Refers To	Cells
MonthlyRate	=Sheet1!$B$1	G5:G8
WeeklyRate	=Sheet1!$B$2	G5:G8

 

[Lil Stinker]

You're not far off. It's actually equipment rental. I'm trying to design a better quoting system using Excel for users who don't use Excel at all. Trying to keep it simple for them unfortunately means all sorts of complications for me as the designer, I'm finding out. So, the client puts in a request for equipment, our sales people would use this form to provide the client with a quote for the rental.

Whether a client is on a Monthly rental vs Weekly rental is determined by the salesperson and the duration of time they plan to have the equipment out. This is why I give them a drop down to choose the rental duration as opposed to having two separate forms for monthly or weekly. Typically, if someone has equipment out anywhere under three weeks, they will go on a weekly rate. If they plan to have the equipment out longer than that, it would be more cost effective to put them on a monthly rental. It's not an automatic calculation which is why the salesperson needs the option. Some rentals necessitate a monthly rental even if the equipment is only out for two weeks. It's discretionary.

- How we calculate a monthly rental is: one month minimum/4 for the weekly cost/7 for the daily cost. If the rate is $200 per month and the rental period is 1m, 2w, 1d, as in your Row 8 example, it would calculate like 200+(50x2)+(7.14x1)=$307.14, not $300. It's not broken down by the day after the monthly minimum. But your example in Row 7 is correct if they have the equipment out for any period of time less than a month and they are on a monthly rental rate, their cost is the one month minimum.

- How we calculate a weekly rental is the same as your example: one week minimum/7 for the daily cost. So, a $100 per week rental for 1w, 5d would be 100+(14.29x5)=$171.45. But if they're quoted a weekly rate and they only take the equipment out for 2 days, the formula needs to lock in the weekly minimum rate. Whatever that rate is, is determined by the salesperson. In this example, $100. So your weekly examples in Rows 5 & 6 are correct as well.

The problem is our rates vary so I can't really lock them in a Named Range as you have done. Also because the rate varies based on each piece of equipment and the rental term. I've got a long product database on a separate sheet using a VLOOKUP formula to produce the unit rates for Column D.

Your sample set seems to work for the most part as long as it can be adjusted to calculate the monthly rate by first the month, then the week, then the day. Then it just needs to fit into my table format and operate with the data validation drop down in E1.

 

[Lil Stinker]

I've now got this working the way I'd like in terms of prorating monthly and weekly rates correctly. Now, I just need to fix in a minimum rate in the calculation formulas for both the monthly and weekly rental terms. Meaning, if a weekly rental is chosen but the duration is less than 7 days, it should still total the weekly rate in column D rather than breaking it down by the day. Same for when a monthly rental is chosen on the form, if the duration is less than a month, it should still total the monthly rate in column D.

I'm sure I could use an IF statement to do this but how do I get it to actively change based on choosing Weeks or Months from the drop down in E17 & E32? And, in what cells would I need the IF to go?

Below are two samples of the same form indicating a monthly term and a weekly term just to display the different result. It's really meant to be just one form.

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	4w,1d	$207.14		Months	0.00	4.00	1.00	50.00	7.14	207.14		4.00	1.00	28.57	828.57
19		Speaker Set	2	50.00	4w,1d	$103.58		Weeks	0.00	4.00	1.00	12.50	1.79	103.58		4.00	1.00	7.14	414.28
20	Start Date	Keyboard & Mouse Set	1	20.00	4w,1d	$20.71			0.00	4.00	1.00	5.00	0.71	20.71		4.00	1.00	2.86	82.86
21	03/01/22	Computer Tower	1	500.00	4w,1d	$517.86			0.00	4.00	1.00	125.00	17.86	517.86		4.00	1.00	71.43	2071.43
22	End Date
23	03/30/22
24
25
26
27					Subtotal	$849.29
28					Discount	$0.00
29					Net Amount	$849.29
30					TOTAL AMOUNT	$849.29
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