Rental Total using days week and monthly rates

D

ddewaard17

New Member
Joined
Aug 3, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 DateEstimated Off-Rent DateOff-Rent / Move to New Project DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on Rent
Sub-Total Based off of Estimated Duration
8/3/20218/10/20218/17/2021$ 225.00$ 225.00$ 675.00$ 1,550.007?
8/3/20218/10/20218/17/2021$ 320.00$ 320.00$ 905.00$ 2,225.007 ?

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
 
Sort by date Sort by votes
Hi,

Looks like you didn't receive the answer expected because you've reposted your question (getting closed by moderator)
So to have another approach, look at this:

Book1
ABCDEFGHIJKLM
1EquipmentRental Start DateEstimated Off-Rent DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on RentSub-Total Based off of Estimated DurationMonthsWeekDays
2Breaker for Skidsteer / Mini-Ex3-8-202123-8-2021225,00225,00675,001.550,00202.700,00026
3Skidsteer / Mini-Ex W/ Bucket3-8-202114-9-2021320,00320,00905,002.225,00424.410,00114
4Auger Attachment3-8-20211-9-2021130,00130,00360,001.070,00291.570,00041
518" Auger Bit3-8-202110-8-202126,0026,0068,00200,00768,00010
660' Manlift3-8-202126-10-2021392,00392,00952,002.240,00848.120,00232
7Georgia Buggy3-8-202118-8-202162,0062,00196,00504,0014454,00021
8Blind Flange 10" 2500#3-8-202112-8-202199,2299,22220,50525,007418,94012
917.740,94
Sheet2
Cell Formulas
RangeFormula
K2:K8K2=DATEDIF(B2,C2,"M")
L2:L8L2=INT(DATEDIF(B2,C2,"MD")/7)
M2:M8M2=DATEDIF(B2,C2,"MD")-(INT(DATEDIF(B2,C2,"MD")/7)*7)
I2:I8I2=(DATEDIF(B2,C2,"M")*G2)+(INT(DATEDIF(B2,C2,"MD")/7)*F2)+((DATEDIF(B2,C2,"MD")-(INT(DATEDIF(B2,C2,"MD")/7)*7))*E2)
I9I9=SUM(I2:I8)
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Okay so ive been messing with it and got it to work for the most part. the flaw i am having with it now is for example in row one, weekly rate starts on the day at which the weeklyrate/daily rate. see bottom table - where it should be on day 3 that it should change to the weekly rate. when i put in 8/7/2021 (4days it works) but i need to get it to work on the day that the rates start if that makes sense.

I appreciate the your patience with me !
You are much appreciated

Rental Start DateEstimated Off-Rent DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on Rentmonthlyweeklydaily
1
8/3/2021​
8/6/2021​
$225.00​
$225.00​
$675.00​
$1,550.00​
3​
0​
0​
3​
675​
2
8/3/2021​
12/15/2021​
$320.00​
$320.00​
$905.00​
$2,225.00​
134​
4​
3​
1​
11125​
3
9/23/2021​
9/28/2021​
$0.00​
$496.00​
$1,240.00​
$3,150.00​
5​
0​
0​
5​
1240​
day rate starts onwhen weekly rate startswhen the monthly rate starts
1​
3​
16.07407​
corresponds to row 1
1​
2.828125​
17.20994​
corresponds to row2
1​
2.5​
17.78226​
corresponds to row3
 
Upvote 0
Hi,

  1. If you're paying rent on the date the rent start and keep paying till the day the rent ends then your estimated Days on Rent in your example is incorrect and should be 4.
  2. I have no clue what you try to explain with the last three lines of your table.
  3. If you want to rent calculation to include the startdate and end date change the formulas to this:
Book1
ABCDEFGHIJKLM
1EquipmentRental Start DateEstimated Off-Rent DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on RentSub-Total Based off of Estimated DurationMonthsWeekDays
2Breaker for Skidsteer / Mini-Ex3-8-20216-8-2021225,00225,00675,001.550,0020900,00004
3Skidsteer / Mini-Ex W/ Bucket3-8-202115-12-2021320,00320,00905,002.225,004211.725,00416
4Auger Attachment3-8-20211-9-2021130,00130,00360,001.070,00291.700,00042
518" Auger Bit3-8-202110-8-202126,0026,0068,00200,00794,00011
660' Manlift3-8-202126-10-2021392,00392,00952,002.240,00848.512,00233
7Georgia Buggy3-8-202118-8-202162,0062,00196,00504,0014516,00022
8Blind Flange 10" 2500#3-8-202112-8-202199,2299,22220,50525,007518,16013
9Blind Flange 10" 2500#3-8-20214-8-202199,2299,22220,50525,001198,44002
Sheet2
Cell Formulas
RangeFormula
K2:K9K2=DATEDIF(B2,C2+1,"M")
L2:L9L2=INT(DATEDIF(B2,C2+1,"MD")/7)
M2:M9M2=DATEDIF(B2,C2+1,"MD")-(INT(DATEDIF(B2,C2+1,"MD")/7)*7)
I2:I9I2=(DATEDIF(B2,C2+1,"M")*G2)+(INT(DATEDIF(B2,C2+1,"MD")/7)*F2)+((DATEDIF(B2,C2+1,"MD")-(INT(DATEDIF(B2,C2+1,"MD")/7)*7))*E2)
 
Upvote 0
jorismoerings said:
Hi,

  1. If you're paying rent on the date the rent start and keep paying till the day the rent ends then your estimated Days on Rent in your example is incorrect and should be 4.
  2. I have no clue what you try to explain with the last three lines of your table.
  3. If you want to rent calculation to include the startdate and end date change the formulas to this:
Book1
ABCDEFGHIJKLM
1EquipmentRental Start DateEstimated Off-Rent DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on RentSub-Total Based off of Estimated DurationMonthsWeekDays
2Breaker for Skidsteer / Mini-Ex3-8-20216-8-2021225,00225,00675,001.550,0020900,00004
3Skidsteer / Mini-Ex W/ Bucket3-8-202115-12-2021320,00320,00905,002.225,004211.725,00416
4Auger Attachment3-8-20211-9-2021130,00130,00360,001.070,00291.700,00042
518" Auger Bit3-8-202110-8-202126,0026,0068,00200,00794,00011
660' Manlift3-8-202126-10-2021392,00392,00952,002.240,00848.512,00233
7Georgia Buggy3-8-202118-8-202162,0062,00196,00504,0014516,00022
8Blind Flange 10" 2500#3-8-202112-8-202199,2299,22220,50525,007518,16013
9Blind Flange 10" 2500#3-8-20214-8-202199,2299,22220,50525,001198,44002
Sheet2
Cell Formulas
RangeFormula
K2:K9K2=DATEDIF(B2,C2+1,"M")
L2:L9L2=INT(DATEDIF(B2,C2+1,"MD")/7)
M2:M9M2=DATEDIF(B2,C2+1,"MD")-(INT(DATEDIF(B2,C2+1,"MD")/7)*7)
I2:I9I2=(DATEDIF(B2,C2+1,"M")*G2)+(INT(DATEDIF(B2,C2+1,"MD")/7)*F2)+((DATEDIF(B2,C2+1,"MD")-(INT(DATEDIF(B2,C2+1,"MD")/7)*7))*E2)
Click to expand...
i did that but for example if i have this
dailyweeklymonthlyestimated rental days
8/3/2021​
8/6/2021​
$225.00​
$675.00​
$1,550.00​
3​
and i use your formula i got,
0​
0​
3​
$675.00​
 
Upvote 0
steve the fish said:
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.
Click to expand...
Your formula is working ! but if you see in a reply below, youll see the only issue im having - for example the first one, the weekly rate starts on day three and its starting it on day four. if that makes sense
 
Upvote 0
ddewaard17 said:
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 DateEstimated Off-Rent DateOff-Rent / Move to New Project DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on Rent
Sub-Total Based off of Estimated Duration
8/3/20218/10/20218/17/2021$ 225.00$ 225.00$ 675.00$ 1,550.007?
8/3/20218/10/20218/17/2021$ 320.00$ 320.00$ 905.00$ 2,225.007?

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
Click to expand...
ddewaard17 said:
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 DateEstimated Off-Rent DateOff-Rent / Move to New Project DateMinimum RateDaily RatesWeekly RatesMonthly RatesEstimated Days on Rent
Sub-Total Based off of Estimated Duration
8/3/20218/10/20218/17/2021$ 225.00$ 225.00$ 675.00$ 1,550.007?
8/3/20218/10/20218/17/2021$ 320.00$ 320.00$ 905.00$ 2,225.007?

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
Click to expand...
Hi,
Maybe the example below will help you ...
 

Attachments

  • Intervals .PNG
    Intervals .PNG
    33.8 KB · Views: 22
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
144
Asbestos_Jen
Asbestos_Jen
R
  • Question Question
Calculate a total amount based on number of days in period and incremental increases in daily rate
Replies
0
Views
636
Rochelle13
R
J
  • Solved
Daily 3yr CF: Need to flag 4th/last Thursday of each month
Replies
3
Views
239
Fluff
Fluff
D
  • Solved
Auto fill dates and days of the week from month
Replies
3
Views
263
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top