DATEDIF formula that prorates

[Lil Stinker]

Hey Folks-
I need help coming up with a formula that can calculate the weekly rental cost based on a one week minimum whether the rental term is a few days or, a week and a few days.

Basically, if they rent equipment for anywhere from 1-7 days, there is the weekly minimum cost of $150. However, if they extend over a week to, let's say, 10 days, then it needs to prorate to a daily cost ($21.43/day) after the one week minimum is met. So, a 10-day rental would come to $214.29, while a 4-day rental would be $150. Rates will vary, so $150 is just an example here.

Currently, part of our equipment rents on a one day, one week format so even if they rent equipment for 10 days, the cost will be for two weeks total. I use this DATEDIF formula below to calculate the weekly rental period there. Then I just multiply the cells to get the Subtotal. No issues.
=ROUNDUP((DATEDIF($E$11,$E$13,"d")/7),0))

However, if I try =ROUNDUP((DATEDIF($E$11,$E$13,"d")/7),2)), it breaks the week down in decimals, which is fine, but I don't know to lock in that first week and then start prorating only after 7 days.

Any suggestions would be greatly appreciated.

Sample table:

Book2
	E	F	G	H	I	J	K	L
7		Description	Column1	Column2	QTY	Unit Rate	Weeks	Item Subtotal
8	Pickup Date	Walkie Talkie			1	5.00	1.00	$5.00
9	2/14/2022
10	Billing Start
11	2/14/2022
12	Billing End
13	2/18/2022
14	Return Date
15	2/19/2022
16
17
18
19							Subtotal	$5.00
22							TOTAL AMOUNT	$5.00
Sheet 1

Cell Formulas
Range	Formula
K8:K18	K8	=IF(ISBLANK(I8),"",ROUNDUP((DATEDIF($E$11,$E$13,"d")/7),0))
L8:L18	L8	=IF(ISBLANK(I8),"",I8*J8*K8)
L19	L19	=SUM(Table1[Item Subtotal])
L22	L22	=L21

 

[Micron]

Not sure what this means:

if they rent equipment for 10 days, the cost will be for two weeks total.

Wouldn't that be $300 (150*2)?

If only using 3 cells (2 for dates and 1 for calculation), what about =IF(DAYS(D10,C10)>7,DAYS(D10,C10)*21.43,150)
where D10 is the return date, C10 the start date.

 

[bebo021999]

Just get rate*days/7, then compare with 150, take the larger:

=MAX(J8,DATEDIF($E$11,$E$13,"d")*J8/7)

 

[Lil Stinker]

Not sure what this means:

Wouldn't that be $300 (150*2)?

If only using 3 cells (2 for dates and 1 for calculation), what about =IF(DAYS(D10,C10)>7,DAYS(D10,C10)*21.43,150)
where D10 is the return date, C10 the start date.

Hello Micron-
We have two rental structures. One where equipment rented on a weekly basis is rounded up to a full week no matter whether you have the equipment out for 10 days or 14. So in that example, $150 for two weeks would come to $300.
The other structure includes prorating after the first week minimum, so the subtotal for a 10 day rental would be 150+(21.43*3)=$214.29, this is where I need the formula.
I can't use your formula because it includes the specific rate in the formula and rates will vary. It needs to be based on whatever rate is put in the Unit Rate column. In theory the formula works but it's not fluid.

Thanks for your input!

 

[Lil Stinker]

Just get rate*days/7, then compare with 150, take the larger:

=MAX(J8,DATEDIF($E$11,$E$13,"d")*J8/7)

Not sure what you mean by "take the larger" but the formula seems to work!

Thank you so much

 

[Micron]

Really, you don't say if the cell address for the rate is a constant with J8 or whatever, so it's up to you to swap that??
Doesn't matter - the other formula seems better to me anyway.