Date yearly series generation

[Acekay]

Hi all,

I need to generate date series in columnar form for many of Bond series from the date of purchase till Maturity date.
I do not want to use fill handle as for all bond series i can not use. Following is the required sheet;


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Buy Date[/TD]
[TD]Maturity Date[/TD]
[TD]Year Left[/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[TD]Year 5[/TD]
[TD]Year 6[/TD]
[TD]Year 7[/TD]
[TD]Year 8[/TD]
[/TR]
[TR]
[TD]Series1[/TD]
[TD]30/04/2018[/TD]
[TD]25/10/2023[/TD]
[TD]5[/TD]
[TD]25/10/18[/TD]
[TD]25/10/19[/TD]
[TD]25/10/20[/TD]
[TD]25/10/21[/TD]
[TD]25/10/22[/TD]
[TD]25/10/23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Series2[/TD]
[TD]31/01/2016[/TD]
[TD]13/01/2024[/TD]
[TD]8[/TD]
[TD]13/01/17[/TD]
[TD]13/01/18[/TD]
[TD]13/01/19[/TD]
[TD]13/01/20[/TD]
[TD]13/01/21[/TD]
[TD]13/01/22[/TD]
[TD]13/01/23[/TD]
[TD]13/01/24[/TD]
[/TR]
[TR]
[TD]Series3[/TD]
[TD]25/09/2015[/TD]
[TD]25/12/2022[/TD]
[TD]7[/TD]
[TD]25/12/15[/TD]
[TD]25/12/16[/TD]
[TD]25/12/17[/TD]
[TD]25/12/18[/TD]
[TD]25/12/19[/TD]
[TD]25/12/20[/TD]
[TD]25/12/21[/TD]
[TD]25/12/22[/TD]
[/TR]
</tbody>[/TABLE]

please help me to generate formula from year 1 onward column.

Thankng you in advance.

 

[steve the fish]

Re: Date yearly series generatin

This formula produces same dates as your examples:

=IF(DATE(YEAR($B2)+(EDATE($C2,-$D2*12)<=$B2)+COLUMNS($A$1:A1)-1,MONTH($C2),DAY($C2))>$C2,"",DATE(YEAR($B2)+(EDATE($C2,-$D2*12)<=$B2)+COLUMNS($A$1:A1)-1,MONTH($C2),DAY($C2)))

 

[jtakw]

Re: Date yearly series generatin

Hi,

For your Series2 sample, shouldn't the "Year Left" be 7 rather than 8 ??, since "Buy Date" is After the "Maturity Date" given the same Year...

Assuming that's a typo:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Buy Date	Maturity Date	Year Left	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8
2	Series1	4/30/2018	10/25/2023	5	10/25/2018	10/25/2019	10/25/2020	10/25/2021	10/25/2022	10/25/2023
3	Series2	1/31/2016	1/13/2024	7	1/13/2017	1/13/2018	1/13/2019	1/13/2020	1/13/2021	1/13/2022	1/13/2023	1/13/2024
4	Series3	9/25/2015	12/25/2022	7	12/25/2015	12/25/2016	12/25/2017	12/25/2018	12/25/2019	12/25/2020	12/25/2021	12/25/2022
Sheet396

Cell Formulas
Range	Formula
E2	=IF(EDATE($C2,-($D2-COLUMNS($E2:E2)+1)*12)>$C2,"",IF(COLUMNS($E2:E2)=1,EDATE($C2,-($D2-1-IF(EDATE($C2,-$D2*12)<$B2,1,0)+1)*12),EDATE(D2,12)))

Formula copied down and across.

 

[Acekay]

Re: Date yearly series generatin

Hi,
Thanx a lot for the quick reply. All are genius here.

 

[jtakw]

Re: Date yearly series generatin

You're welcome.

 

[Acekay]

Date series generation from F column onwards subject to criteria of Raw F1 onwards

hi,
for the same type of sample, please help to put formula for the following table.

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="align: center"]J[/TH]
[TH="align: center"]K[/TH]
[TH="align: center"]L[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]Buy Date[/TD]
[TD]Maturity Date[/TD]
[TD]Year Left[/TD]
[TD]31-03-2015[/TD]
[TD]31-03-2016[/TD]
[TD]31-03-2017[/TD]
[TD]31-03-2018[/TD]
[TD]31-03-2019[/TD]
[TD]31-03-2020[/TD]
[TD]31-03-2021[/TD]
[TD]31/03/2022[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Series1[/TD]
[TD="align: right"]30/04/2018[/TD]
[TD="align: right"]25/10/2023[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25/10/2018[/TD]
[TD="align: right"]25/10/2019[/TD]
[TD]25/10/2020[/TD]
[TD]25/10/2021[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Series2[/TD]
[TD="align: right"]31/01/2016[/TD]
[TD="align: right"]13/01/2024[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13/01/2017[/TD]
[TD="align: right"]13/01/2018[/TD]
[TD="align: right"]13/01/2019[/TD]
[TD="align: right"]13/01/2020[/TD]
[TD="align: right"]13/01/2021[/TD]
[TD="align: right"]13/01/2022[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Series3[/TD]
[TD="align: right"]31/10/2017[/TD]
[TD="align: right"]13/01/2024[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13/01/2018[/TD]
[TD="align: right"]13/01/2019[/TD]
[TD="align: right"]13/01/2020[/TD]
[TD="align: right"]13/01/2021[/TD]
[TD="align: right"]13/01/2022[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Series4[/TD]
[TD="align: right"]25/09/2015[/TD]
[TD="align: right"]25/12/2022[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]25/12/2016[/TD]
[TD="align: right"]25/12/2016[/TD]
[TD="align: right"]25/12/17[/TD]
[TD="align: right"]25/12/2018[/TD]
[TD="align: right"]25/12/2019[/TD]
[TD="align: right"]25/12/2020[/TD]
[TD="align: right"]25/12/2021[/TD]
[TD="align: right"]25/12/2022[/TD]
[/TR]
</tbody>[/TABLE]


Please help

 

[MARK858]

Re: Date series generation from F column onwards subject to criteria of Raw F1 onwards

I think you will probably have to explain the logic of the results rather than just state...

please help to put formula for the following table

 

[njimack]

Re: Date series generation from F column onwards subject to criteria of Raw F1 onwards

Without you explaining the logic, this may be what you're after although my results differ slightly from yours.

Enter the following into E2 and copy down / across as required:

=IF(AND(E$1>=$B2,E$1<=$C2),DATE(YEAR(E$1)-IF(MONTH(E$1)<MONTH($C2),1,0),MONTH($C2),DAY($C2)),"")

 

[Macropod]

Acekay: As per Rule 12 (http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html ), kindly don't post the same question in multiple threads. Threads merged.

 

[Acekay]

Re: Date series generation from F column onwards subject to criteria of Raw F1 onwards

Hi,
This is basically bond interest chart where interest is received annually on maturity dates every year. The logic is that if the maturity date is falling below(less than) column header year end date then maturity date should reflect in that cell. Here year end date criteria would be 1st April to 31st March for respective column.
eg. in my example of Series 2 buy date is 31/01/2016 and maturity date is 13/01/2024, so its first year is interest would be for the year end 31-03-2017 as first interest will be received on 13/01/2017.
eg in series 3 buy date is 31/10/17 and maturity is 13/01/2024 so first year of interest would be for the year end 31-03-2018 as first interest will be received on 13/01/2018.

Your formula is good. But not fulfilling my crieteria as you mentioned that i have not explained logic earlier. Now its explained above. Please help to have a correct formula to give required result.
Do write back for any query.

Thanx.