Benefits released by year and Month

[anawarulhassan]

Hi All,

I am working on file which has benefits start date for example 01/01/17 and finish date of 30/04/7 with a benefit value $10,000 and in other cases start date 01/10/17 an end date 30/10/18 Benefit value $12,000.

I am trying to capture cost by year and month on pro rata basis. So I can check how much benefit was realised in April 2018 but only for 2018 related period.

Advance thanks for your help.

 

[Dave Patton]

I am not sure exactly what you require; two different approaches are shown.
Extend the formulas and dates to the right as required.

Excel 2010
	B	C	D	E	F
1	Version 1
2	Start	Stop	Amt	Jan-17	Feb-17
3	01-Jan-17	30-Apr-17	10,000.00	2,583.33	2,333.33
4	01-Oct-17	31-Oct-18	12,000.00	0	0.00
5
6	Version 2	Months	Per Month	2017	2018
7		4	2,500.00	10,000.00	0
8		13	923.08	2,769.23	9,230.77
9
10	Dates in row 3 are like Jan 1 2017.
11	Dates in row 6 are like Dec 31, 2017.
7aa (2)

Cell Formulas
Range	Formula
E3	=MAX(0, MIN($C3, EOMONTH(E$2, 0)) - MAX($B3, E$2) + 1) * $D3 / ($C3 - $B3 + 1)
E4	=MAX(0, MIN($C4, EOMONTH(E$2, 0)) - MAX($B4, E$2) + 1) * $D4 / ($C4 - $B4 + 1)
E7	=DATEDIF($B3,MIN($C3+1,E$6+1),"m")*D7
E8	=DATEDIF($B4,MIN($C4+1,E$6+1),"m")*D8
F3	=MAX(0, MIN($C3, EOMONTH(F$2, 0)) - MAX($B3, F$2) + 1) * $D3 / ($C3 - $B3 + 1)
F4	=MAX(0, MIN($C4, EOMONTH(F$2, 0)) - MAX($B4, F$2) + 1) * $D4 / ($C4 - $B4 + 1)
C7	=DATEDIF(B3,C3+1,"m")
C8	=DATEDIF(B4,C4+1,"m")
D7	=D3/C7
D8	=D4/C8

 

[anawarulhassan]

I am not sure exactly what you require; two different approaches are shown.
Extend the formulas and dates to the right as required.

Excel 2010

	B	C	D	E	F
Version 1
Version 2	Months	Per Month
Dates in row 3 are like Jan 1 2017.
Dates in row 6 are like Dec 31, 2017.

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Stop[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Amt[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Jan-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Feb-17[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]01-Jan-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30-Apr-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10,000.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] , align: right"]2,583.33[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] , align: right"]2,333.33[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]01-Oct-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]31-Oct-18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12,000.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] , align: right"]0.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2,500.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10,000.00[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]923.08[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2,769.23[/TD]
[TD="align: right"]9,230.77[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

7aa (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=MAX(0, MIN($C3, EOMONTH(E$2, 0)) - MAX($B3, E$2) + 1) * $D3 / ($C3 - $B3 + 1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=MAX(0, MIN($C3, EOMONTH(F$2, 0)) - MAX($B3, F$2) + 1) * $D3 / ($C3 - $B3 + 1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=MAX(0, MIN($C4, EOMONTH(E$2, 0)) - MAX($B4, E$2) + 1) * $D4 / ($C4 - $B4 + 1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=MAX(0, MIN($C4, EOMONTH(F$2, 0)) - MAX($B4, F$2) + 1) * $D4 / ($C4 - $B4 + 1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=DATEDIF(B3,C3+1,"m")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=D3/C7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=DATEDIF($B3,MIN($C3+1,E$6+1),"m")*D7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=DATEDIF(B4,C4+1,"m")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=D4/C8[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=DATEDIF($B4,MIN($C4+1,E$6+1),"m")*D8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks for prompt reply....I will try to copy what you have done and fingers crossed all be k

 

[Dave Patton]

re: your additional questions
"I am getting the whole amount in 2018. £10,000 and £12,000 is appearing for both lines respectively and not showing 0 and 9230.77 as you have shown. I simply copied the formula from E7 to F7 and E8 to F8."

a) you can create a distribution similar to the monthly shown above but for years
b) prepare the summary with arithmetic.

" I have one more question, what if I want to show the number as year to date. For example, 01 Oct 17 - 31 Oct 18 but the value for January to July cumulatively as at 31 July 2018 but only for 2018 months.

a) You have the information by month, you can prepare a report and assemble the numbers as you require.

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H	I
8	Start	End	Amount	2017	2018	2019	2020	2021
9	01-Jan-17	30-Apr-17	10,000.00	10,000.00	0.00	0.00	0.00	0.00
10	01-Oct-17	31-Oct-18	12,000.00	2,787.88	9,212.12	0.00	0.00	0.00
11	01-Jan-17	31-Dec-21	1,826.00	365.00	365.00	365.00	366.00	365.00
7aaa

Cell Formulas
Range	Formula
E9	=MAX(0, MIN($C9, EOMONTH(E$8, 11)) - MAX($B9, E$8) + 1) * $D9 / ($C9 - $B9 + 1)
F9	=MAX(0, MIN($C9, EOMONTH(F$8, 11)) - MAX($B9, F$8) + 1) * $D9 / ($C9 - $B9 + 1)
G9	=MAX(0, MIN($C9, EOMONTH(G$8, 11)) - MAX($B9, G$8) + 1) * $D9 / ($C9 - $B9 + 1)
H9	=MAX(0, MIN($C9, EOMONTH(H$8, 11)) - MAX($B9, H$8) + 1) * $D9 / ($C9 - $B9 + 1)
I9	=MAX(0, MIN($C9, EOMONTH(I$8, 11)) - MAX($B9, I$8) + 1) * $D9 / ($C9 - $B9 + 1)