Formula to distribute Weekly Savings between "Start Date" & "End Date" across calendar year

[MR_EXCEL_16]

Hello Friends! I am new to excel, need your help. Here is the situation...

Total Savings: E.g. 1 ... $2898 needs to equally spread between May'20 till Jun'20. Months prior and after Start & End Date needs to reflect 0.

S. No.

Total Savings

Start Date

End Date

Total Weeks

Apr'20

May'20

Jun'20

Jul'20

Aug'20

Sep'20

Oct'20

Nov'20

Dec'20

1​

$2,898​

5/15/2020​

7/20/2020​

7​

2​

$3,577​

6/12/2020​

6/28/2020​

1​

Thank you in advance.

 

[marshy3300]

Why is end date 7/20/20 (July) but you say until June?

 

[MR_EXCEL_16]

Why is end date 7/20/20 (July) but you say until June?

Because savings is only for 7 weeks in between May - Jul. Thanks.

 

[Dave Patton]

T202007b.xlsm
	A	B	C		E				I	J	K	L	M	N	O	P	Q
1										Distribute to # of months
2		Total Savings	Start date		Stated Number of Months				Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3		$2,898	15-05-20		7					414.00	414.00	414.00	414.00	414.00	414.00	414.00
4		$2,000	12-06-20		1						2,000.00
5
4a

Cell Formulas
Range	Formula
I3:Q4	I3	=IF(AND(MONTH($C3)<=MONTH(I$2),$E3-COUNT($F3:H3)>0),$B3/$E3,"")

 

[MR_EXCEL_16]

Fantastic. This worked Thank you so very much Dave Patton.

 

[Dave Patton]

Thanks for the feedback.
You can review the solution below.

H2:Q2

are dates like Mar 31, 2020 .. Dec 31, 2020

formatted at MMM

T202007b.xlsm
	C		E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Start date		Stated Number of Months	Calculated End Date		Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	15-May-20		7	15-Nov-20				414.00	414.00	414.00	414.00	414.00	414.00	414.00
4	12-Jun-20		1	12-Jun-20					2,000.00
4a

Cell Formulas
Range	Formula
H3:Q4	H3	=IF(AND($C3<=H$2,SUM($G3:G3)<$B3),$B3/$E3,"")
F3:F4	F3	=EDATE(C3,E3-1)