Rental Total using days week and monthly rates

[ddewaard17]

Hey

So i am trying to create a formula that automatically finds the sub total rate based on how long i have the equipment rented for.

Rental Start Date	Estimated Off-Rent Date	Off-Rent / Move to New Project Date	Minimum Rate	Daily Rates	Weekly Rates	Monthly Rates	Estimated Days on Rent	Sub-Total Based off of Estimated Duration
8/3/2021	8/10/2021	8/17/2021	$ 225.00	$ 225.00	$ 675.00	$ 1,550.00	7	?
8/3/2021	8/10/2021	8/17/2021	$ 320.00	$ 320.00	$ 905.00	$ 2,225.00	7	?

I have tried a couple different approaches to creating this formula and it ends up being that it'll calculate the number of days or months but never include the weeks

The other import note on this, is that i will always have a different rate day - meaning the weekly rate starts at the day of which the weekly/daily. so like for the first row with 675/225 = 3 (meaning if i have the equipment for 3 days or longer, the company will charge me for the week rate) but if i have the equipment for a week and two days, well then the company will bill me for a week rate plus two day rates.

Same goes for the monthly rate. in the first row my mothy rate starts at 1550/675 = 2.296 weeks or 17.07 days. but if i have the equipment for one month two weeks and one day, then i need it to compute a total for the sum of a monthly rate + two weekly rates + a daily rate. Any help is appreciated THANK YOU
i
I have been able to create a forula that only works for teh

 

[steve the fish]

Shall we use some helper columns:

Months in I2:

=DATEDIF(A2,B2,"m")

Weeks in J2:

=INT((B2-EDATE(A2,I2)+1)/7)

Days in K2:

=MOD(B2-EDATE(A2,I2)+1,7)

Then in L2:

=MIN((I2*G2)+(J2*F2)+(K2*E2),(I2*G2)+((J2+1)*F2),((I2+1)*G2))

assumes your example is in A1:H3

 

[ddewaard17]

Kind of, but i want to be able to input it into the subtotal amount area, and the billing rate is a 28 day cycle. i.e. i have the equipment for 30 days, then the company will charge me for a monthly rate plus two of the daily rates.

This is where i am at as far as the coding is concerned.


=MIN((DATEDIF($H3-1,$I3,"m")+1)*VLOOKUP($A3,$A:$R,18,0),(INT(DATEDIF($H3-1,$I3,"d")/28)*VLOOKUP($A3,$A:$R,18,0))+MIN(VLOOKUP($A3,$A:$R,18,0),INT(MOD(DATEDIF($H3-1,$I3,"d"),28)/7)*VLOOKUP($A3,$A:$R,17,0))+MIN(VLOOKUP($A3,$A:$R,17,0),MOD(DATEDIF(H3-1,$I3,"d"),7)*VLOOKUP($A3,$A:$R,16,0)))

The formula from above is located in cell F2. I inserted these two to kind of mess around with the formula.

Equipment	Rental Start Date	Estimated Off-Rent Date	Projected Off-Rent / Move to New Project Date	Number of Days	Days	Weeks	Months	Minimum Rate	Daily Rates	Weekly Rates	Monthly Rates	Estimated Days on Rent	Sub-Total Based off of Estimated Duration
Breaker for Skidsteer / Mini-Ex	8/3/2021	8/10/2021	8/17/2021	14.00	900.00		0.00	$ 225.00	$ 225.00	$ 675.00	$ 1,550.00	7
Skidsteer / Mini-Ex	8/3/2021	8/10/2021	8/17/2021	14.00				$ 320.00	$ 320.00	$ 905.00	$ 2,225.00	7
Auger Attachment	8/3/2021	8/10/2021	8/10/2021					$ 130.00	$ 130.00	$ 360.00	$ 1,070.00	7
18" Auger Bit	8/3/2021	8/10/2021	8/10/2021					$ 26.00	$ 26.00	$ 68.00	$ 200.00	7
60' Manlift	8/3/2021	8/10/2021	8/10/2021					$ 392.00	$ 392.00	$ 952.00	$ 2,240.00	7
Georgia Buggy	8/3/2021	8/17/2021	8/17/2021					$ 62.00	$ 62.00	$ 196.00	$ 504.00	14

 

[steve the fish]

Why are you using vlookup??

 

[ddewaard17]

Why are you using vlookup??

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Today's Date:	Tuesday, August 3, 2021			.							0.00	0.00		1.00	3.00	16.07
2	Line#	Requested By	Vendor	Quote / Contract Number	Purchase Order	PO Committed	Equipment	Rental Start Date	Estimated Off-Rent Date	Projected Off-Rent / Move to New Project Date	Number of Days	Days	Weeks	Months	Minimum Rate	Daily Rates	Weekly Rates	Monthly Rates
3	1						Breaker for Skidsteer / Mini-Ex	8/3/2021	8/10/2021	8/17/2021	14.00	900.00		0.00	$ 225.00	$ 225.00	$ 675.00	$ 1,550.00
4	2						Skidsteer / Mini-Ex	8/3/2021	8/10/2021	8/17/2021	14.00				$ 320.00	$ 320.00	$ 905.00	$ 2,225.00
5	3						Auger Attachment	8/3/2021	8/10/2021	8/10/2021					$ 130.00	$ 130.00	$ 360.00	$ 1,070.00
6	4						18" Auger Bit	8/3/2021	8/10/2021	8/10/2021					$ 26.00	$ 26.00	$ 68.00	$ 200.00
7	5						60' Manlift	8/3/2021	8/10/2021	8/10/2021					$ 392.00	$ 392.00	$ 952.00	$ 2,240.00
8	6						Georgia Buggy	8/3/2021	8/17/2021	8/17/2021					$ 62.00	$ 62.00	$ 196.00	$ 504.00
9	7						Blind Flange 10" 2500#	8/4/2021	8/11/2021	8/11/2021					$ 99.22	$ 99.22	$ 220.50	$ 525.00
10	8

I got the idea from here Billing Calculator

 

[steve the fish]

Ok so now im basing it on your first example cause things seemed to have moved!

I2: =INT(H2/28)
J2: =INT((H2-(I2*28))/7)
K2: =H2-(I2*28)-(J2*7)
L2: =MIN((I2*G2)+(J2*F2)+(K2*E2),(I2*G2)+((J2+1)*F2),((I2+1)*G2))

And you dont need vlookup. You already have the rates in the cells of the same row far as i can tell. Directly refer to them.

This is based on a 28 day month so eventually if you have a long term hire you will pay for an 'extra' month.

 

[ddewaard17]

when i plugged that in, i got $1046250.00

 

[steve the fish]

Look at what you supplied in post 1 then apply what i gave. You absolutely will get what you asked for. If you dont reread post number 1.

 

[ddewaard17]

Look at what you supplied in post 1 then apply what i gave. You absolutely will get what you asked for. If you dont reread post number 1.

I used post one since thats what you were going off of when conducting the formula and thats how i ended up with that number

 

[steve the fish]

It just works if you use the data you supplied in post 1 and the formulas i gave you. Dont know what else i can say. No idea how you have come up with that number but its impossible from your posted data.