Need help with Pro-Rate Formula

[ENAPo]

What formula could I use to calculate the Total Amount based on the Price and Dates given? I am using Excel 2010. And the pro-rate within the same month, I can manage. It's the 2nd line that crosses multiple months that is not working.

Customer	Item	Monthly Rate	Qty.	Total Monthly Price	Start	End	Total Amount
Bert	A5009	300.00	1.00	300.00	7/1/14	7/10/14	??
Bert	A5009	300.00	2.00	600.00	7/11/14	6/30/15	??

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

Thank you!

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

 

[Michael M]

Maybe this

Excel 2007
	A	B	C	D	E	F	G	H
1	Customer	Item	Monthly	Qty.	Total Monthly	Start	End	Total Amount
2			Rate		Price
3	Bert	A5009	300	1	300	7/01/2014	7/10/2014	9
4	Bert	A5009	300	2	600	7/11/2014	20/06/2015	7
Sheet3

Cell Formulas
Range	Formula
H3	=(YEAR(G3)-YEAR(F3))*12+MONTH(G3)-MONTH(F3)
H4	=(YEAR(G4)-YEAR(F4))*12+MONTH(G4)-MONTH(F4)

 

[Michael M]

Oops, missed a bit...

Excel 2007
	A	B	C	D	E	F	G	H
1	Customer	Item	Monthly	Qty.	Total Monthly	Start	End	Total Amount
2			Rate		Price
3	Bert	A5009	300	1	300	7/01/2014	7/10/2014	2700
4	Bert	A5009	300	2	600	7/11/2014	30/06/2015	4200
Sheet3

Cell Formulas
Range	Formula
H3	=((YEAR(G3)-YEAR(F3))*12+MONTH(G3)-MONTH(F3))*E3
H4	=((YEAR(G4)-YEAR(F4))*12+MONTH(G4)-MONTH(F4))*E4

 

[ENAPo]

Oops, missed a bit...


Excel 2007

	A	B	C	D	E	F	G	H
1	Customer	Item	Monthly	Qty.	Total Monthly	Start	End	Total Amount
2			Rate		Price
3	Bert	A5009	300	1	300	7/01/2014	7/10/2014	2700
4	Bert	A5009	300	2	600	7/11/2014	30/06/2015	4200

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula H3 =((YEAR(G3)-YEAR(F3))*12+MONTH(G3)-MONTH(F3))*E3 H4 =((YEAR(G4)-YEAR(F4))*12+MONTH(G4)-MONTH(F4))*E4 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you for your help but it does not calculate the Line#3 correctly. I need it to calculate the months as well as the days 7/11/2014 - 6/30/2015 =