Split one amount over 12 months based on date range

[dogpile]

The data I have is:
Column A: Start Date
Column B: End Date
Column C: Amount

I would like the annual amount shown monthly (amount split evenly over the range of start and end date) in column E:P by a formula. A formula that can be copied down mulitple rows.

i.e.: start date is April, end date is June, amount is $9,000.
Result would be Jan-Mar $0, Apr $3,000, May $3,000, June $3,000, July-Dec $0

Thanks in advance.
J

 

[schielrn]

I assume this cannot go accross years, so something like this should work:

Not really sure how your sheet is set up and how the start and end dates are entered?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Start Date	End Date	Amount		January	February	March	April	May	June	July	August	September	October	November	December
2	4/1/2010	6/30/2010	9000		0	0	0	3000	3000	3000	0	0	0	0	0	0
3	2/1/2010	8/1/2010	21000		0	3000	3000	3000	3000	3000	3000	3000	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
E2	=IF(AND((E$1&" 1, "&YEAR($A2))+0>=$A2,(E$1&" 1, "&YEAR($A2))+0<=$B2),$C2/(MONTH($B2)-MONTH($A2)+1),0)

Hope that helps.

 

[dogpile]

It certainly does, thank you for your time.
J

 

[Riaav]

Can someone please help me? I want to divide the amount in the # of months specified like A2 = 3 so i need to divide it in 3 (jan, Feb, Mar) months equally. I know the formula E2/A2 but I want to make it automated depending upon the # of months the amount should be divided.

Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]E[/TH]
[TH]F
[/TH]
[TH]G[/TH]
[TH]H
[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[TH]N[/TH]
[TH]O[/TH]
[TH]P[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]# of Months
[/TD]
[TD]Amount
[/TD]
[TD][/TD]
[TD][/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD] 3
[/TD]
[TD]7000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD] 6
[/TD]
[TD]800
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please help!!

Thanks in advance.

It certainly does, thank you for your time.
J

 

[Rick Rothstein]

Can someone please help me? I want to divide the amount in the # of months specified like A2 = 3 so i need to divide it in 3 (jan, Feb, Mar) months equally. I know the formula E2/A2 but I want to make it automated depending upon the # of months the amount should be divided.

Sheet1[TABLE="class: html-maker-worksheet"]
<TBODY>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[TH]H
[/TH]
[TH]I
[/TH]
[TH]J
[/TH]
[TH]K
[/TH]
[TH]L
[/TH]
[TH]M
[/TH]
[TH]N
[/TH]
[TH]O
[/TH]
[TH]P
[/TH]
[/TR]
[TR]
[TH]1
[/TH]
[TD]# of Months
[/TD]
[TD]Amount
[/TD]
[TD][/TD]
[TD][/TD]
[TD]January
[/TD]
[TD]February
[/TD]
[TD]March
[/TD]
[TD]April
[/TD]
[TD]May
[/TD]
[TD]June
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[TD]September
[/TD]
[TD]October
[/TD]
[TD]November
[/TD]
[TD]December
[/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD]3
[/TD]
[TD]7000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD]6
[/TD]
[TD]800
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

You want the starting value to always be in January?

 

[Riaav]

There is one exception where the starting value has to start from the end of the month which is December. Can you please explain that as well?


Thanks so much!

You want the starting value to always be in January?

 

[skippy099]

Hi,

Has any one tried what "schielrn suggested?
I replicated his formula example in a blank sheet and it did not work just showed #VALUE!
Do certain cells have to be formatted in certain ways too eg. DATE, Currency etc?

If someone could give any suggestion based on his example that would be so hopeful! I have hundreds of expenses which are monthly, quarterly or yearly which need to be split into months

Thanks!

 

[schielrn]

The months listed in the columns are actual text months and not a date field. So make sure in my example that E1 is actually January and not 01/DD/YYYY formatted to just show the month.

If that is not the case then post back how exactly you have your data.

 

[skippy099]

Great one
Thanks that did the trick.

 

[omkarnik]

Great one
Thanks that did the trick.

Thank you!! i found the post extremely useful. I have data which is split over the years, can you please help.

Thanks