Need help creating formula that prorates a quarterly cost based on date

[QueenQ]

I have created a parking space rental lease that is based on a quarterly billing cycle. My issue is "stragglers" who wish to sign a lease after the quarter has started. Specifically, there are several lots. Each lot has it's own quarterly rate. I already have the form calculating the number of desired spots x quarterly cost then it calculates the total amount due from the customer. But how do I prorate this formula to acknowledge the effective date of the contract's impact on the rate? So, that the customer knows how much is due as of the effective date forward?

For example, the quarter is 4/1/22 - 6/30/22. If the person begins their lease 4/12/22, I need the formula to calculate the # of parking spots x the remaining time left during the current quarter.

Please help! I've tussled back and forth with this thing all afternoon!

Thanks

 

[BSALV]

Map1.xlsm
	A	B
1	normal start	4/1/22
2	normal stop	6/30/22
3	new start	4/12/22
4	normal price	1.000,00 €
5	new price	879,12 €
Blad3

Cell Formulas
Range	Formula
B5	B5	=+B4*(B2-B3+1)/(B2-B1+1)

 

[QueenQ]

Thank you so very much! I'm not getting the proper figure though. Perhaps a snippet of my draft could help me better explain? I'm trying to make the prorated amount populate in column H based on the effective date of the initiated lease. Please see attachment.

 

[BSALV]

How does column H knows that's a monthly or a quarterly rent ?
Can you insert a column (D?), where you add a "M" or a "Q", then in H with a formula, that amount can be calculated.
something like this ???
The green cells in L2:M3 calculate the beginning and end of the month or quarter with the date in E2. This cells, you can set them somewhere to the right, where they don't bother you.
The cell D5 is with data-validation, so you can only choose for M, Q, m, q or blank.
The cells E5:F5 have a custom numberformat to show the text behind that number. So you only have to enter the amount, excel adds the "/space", the numberformat is $ 0.00 " / space" (in the image here below, it's with a comma, because i have other regional settings)

Map3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1												start	end
2			Effective Date		4/13/2022						month	4/1/2022	4/30/2022
3											quarter	4/1/2022	6/30/2022
4		number of spaces		Month/Quarter	month cost	Quarter Cost	Amount Due
5		1		q	$ 40,00 / space	$ 120,00 / space	$ 104,18
6
Blad1

Cell Formulas
Range	Formula
L2	L2	=EOMONTH(E2,-1)+1
M2	M2	=+EOMONTH(E2,0)
L3	L3	=DATE(YEAR(E2),FLOOR.MATH(MONTH(E2)-1,3)+1,1)
M3	M3	=+EDATE(L3,3)-1
G5	G5	=IF(D5="",0,IF(D5="M",($M$2-$E$2+1)/($M$2-$L$2+1)*E5,($M$3-$E$2+1)/($M$3-$L$3+1)*F5))*B5

Cells with Data Validation
Cell	Allow	Criteria
D5	List	M;Q;m;q