distribution of expense over 12 month period

[shamsu203]

Hi
I have a work sheet which contains the total yearly expense. I want to allocate this expense for the no of months completed in the year in other sheet.
Eg
Expense sheet row a1 usd 4300/
income expense sheet starting from jan to jul this amount of 4300 need to be proportionaly show for this period as =(4300/12)*7. in aug the no to divide will be 8
what is the formula to use.
Regrds
Shamsu

 

[Dave Patton]

H2:S2 are dates like Jan 31, 2020 .. Dec 31, 2020; they are formatted “MMM”.

T202007b.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Amount	Start date	End Date	Stated Number of Months	Term		Jan	Feb
3		$4,300	01-Jan-20	31-Dec-20	7	12		358.33	358.33
4		$2,000	20-Feb-20	20-Jun-20	5	4			500.00
4aa

Cell Formulas
Range	Formula
H3:I4	H3	=IF(AND($C3<=H$2,SUM($G3:G3)<$B3),$B3/$F3,"")
F3:F4	F3	=DATEDIF(C3-1,D3,"m")

 

[shamsu203]

Hi
Tks exactly what I wanted

 

[Dave Patton]

T202007b.xlsm
	A	B	C	D	E	F	G	H	I	J
1								Distribute to Stated # of months
2		Amount	Start date	End Date	Stated Number of Months	Term		Jan	Feb	Mar
3		$4,300	01-Jan-20	31-Dec-20	7	12		358.33	358.33	358.33
4		$1,200	15-Jan-20	31-Dec-20	12	12		100.00	100.00	100.00
4aa

Cell Formulas
Range	Formula
H3:J4	H3	=IF(AND($C3<=H$2,COUNTIF($G3:G3,">0")<$E3),$B3/$F3,"")
F3:F4	F3	=DATEDIF(C3-DAY(C3),D3,"m")

 

[shamsu203]

Hii
Tks for ur reply. what formula will give me total of seven months in h3. this will change if e 3 change to 8 months (ie the start date to todays date.
Regrds
Shamsu

 

[Dave Patton]

Did you try the formula?
If you change E3 to 8, 358.33 will show in Jan - Aug and that range adds up to 2866.67. You can have a cell such as T3 that would give the total =SUM(H3:S3)
Try reviewing the formula with Excel's Formula Evaluate in the Formula Auditing area of icons.