determining monthly amounts based on date

[snoozing]

i have a listing of contracts with start & end dates. each contract has a set amount. im trying to create a sheet that would track how much is earned each month based on the dates of the contracts. is this possible with a formula & not vba? below is what im trying to accomplish

Amount	Start	End	Jan	Feb	Mar	Apr
100,000	1/1/12	4/30/12	25,000	25,000	25,000	25,000
40,000	2/1/12	3/31/12	0	20,000	20,000	0

<TBODY>
</TBODY>

 

[snoozing]

nevermind, i figured a workaround

thanks!

 

[snoozing]

bah...my workaround didnt work the way i wanted it too

 

[rob.barnes01]

I think I was able to accomplish what you needed by adding a helper row above the months,entering the following formula in cell D3 and dragging it out:

[/B]=IF(AND(MONTH($B3)<=D$1,MONTH($C3)>=D$1),$A3/(MONTH($C3)-MONTH($B3)+1),0)[B]

Excel 2007

	A	B	C	D	E	F	G
1				1	2	3	4
2	amount	start	end	Jan	Feb	Mar	Apr
3	100,000	1/1/2012	4/30/2012	25000	25000	25000	25000
4	40,000	2/1/2012	3/31/2012		20000	20000

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

Sheet1​

 

[snoozing]

this works beautifully!!!!

only problem is that my contracts have dates extending beyond a year so now i have manually adjust those

but thanks!