Savings Allocation

[SAMSY03]

I've $ Savings from an Action between a 2 Dates, how do i calculate the Savings for the Dates of a Specific Month
What formula can be applied

Start Date

End Date

Action Period Savings

30-Apr-20

31-May-20

30-Jun-20

AMJ '20

31-Jul-20

31-Aug-20

30-Sep-20

JAS '20

31-Oct-20

30-Nov-20

31-Dec-20

OND '20

31-Jan-21

28-Feb-21

31-Mar-21

JFM '21

FY '21

27-Apr-20​

4-Jul-20​

1000

1-May-20​

30-Jun-20​

3000

15-Jul-20​

10-Oct-20​

7400

 

[SAMSY03]

I've $ Savings from an Action between a 2 Dates, how do i calculate the Savings for the Dates of a Specific Month
What formula can be applied

Start Date

End Date

Action Period Savings

30-Apr-20

31-May-20

30-Jun-20

AMJ '20

31-Jul-20

31-Aug-20

30-Sep-20

JAS '20

31-Oct-20

30-Nov-20

31-Dec-20

OND '20

31-Jan-21

28-Feb-21

31-Mar-21

JFM '21

FY '21

27-Apr-20​

4-Jul-20​

1000

1-May-20​

30-Jun-20​

3000

15-Jul-20​

10-Oct-20​

7400

Any help with the Formula will be greatly appreciated

 

[Dave Patton]

T202004a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1						Allocate by days in month
2	Start	End	Days	Amount	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20
3	27-Apr-20	04-Jul-20	69	1,000.00	57.97	449.28	434.78	57.97	0.00	0.00	0.00
4	01-May-20	30-Jun-20	61	3,000.00	0.00	1,524.59	1,475.41	0.00	0.00	0.00	0.00
5	15-Jul-20	10-Oct-20	88	7,000.00	0.00	0.00	0.00	1,352.27	2,465.91	2,386.36	795.45
6
2aa

Cell Formulas
Range	Formula
E3:K5	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))*$D3/$C3
C3:C5	C3	=B3-A3+1

E2 etc are dates like 1-April-2020 formatted as mmm-yy.

 

[SAMSY03]

T202004a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1						Allocate by days in month
2	Start	End	Days	Amount	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20
3	27-Apr-20	04-Jul-20	69	1,000.00	57.97	449.28	434.78	57.97	0.00	0.00	0.00
4	01-May-20	30-Jun-20	61	3,000.00	0.00	1,524.59	1,475.41	0.00	0.00	0.00	0.00
5	15-Jul-20	10-Oct-20	88	7,000.00	0.00	0.00	0.00	1,352.27	2,465.91	2,386.36	795.45
6
2aa

Cell Formulas
Range	Formula
E3:K5	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))*$D3/$C3
C3:C5	C3	=B3-A3+1

E2 etc are dates like 1-April-2020 formatted as mmm-yy.

 

[SAMSY03]

Thank you for the help!
For Some Reason, the formula is not giving the result beyond April
Also the Value Shown in Action Period Saving is derived out of Weekly Saving
Any guidance will be highly appreciated

 

[Dave Patton]

"For Some Reason, the formula is not giving the result beyond April "

You can copy the working solution to a new clean sheet.
Click on the icon below the f(x) in the post and paste to your empty sheet.
Review the formulas and then adapt to your layout if required.