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.


[TABLE="width: 515"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Item[/TD]
[TD]Monthly
Rate[/TD]
[TD]Qty.[/TD]
[TD]Total Monthly
Price[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Total Amount[/TD]
[/TR]
[TR]
[TD] Bert [/TD]
[TD] A5009 [/TD]
[TD]300.00[/TD]
[TD]1.00[/TD]
[TD]300.00[/TD]
[TD]7/1/14[/TD]
[TD]7/10/14[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD] Bert [/TD]
[TD] A5009 [/TD]
[TD]300.00[/TD]
[TD]2.00[/TD]
[TD]600.00[/TD]
[TD]7/11/14[/TD]
[TD]6/30/15[/TD]
[TD] ??
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 515"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Thank you![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Customer	Item	Monthly	Qty.	Total Monthly	Start	End	Total Amount
Rate	Price
Bert	A5009
Bert	A5009

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]7/01/2014[/TD]
[TD="align: right"]7/10/2014[/TD]
[TD="align: right"]2700[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]7/11/2014[/TD]
[TD="align: right"]30/06/2015[/TD]
[TD="align: right"]4200[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]H3[/TH]
[TD="align: left"]=((YEAR(G3)-YEAR(F3))*12+MONTH(G3)-MONTH(F3))*E3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]H4[/TH]
[TD="align: left"]=((YEAR(G4)-YEAR(F4))*12+MONTH(G4)-MONTH(F4))*E4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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 =