Date fill up

[Acekay]

Hi all.
I have one query about filling up the date if few criteria are fulfilled. Table is as follow;

	A	B	C	D	E	F	G	H	I
1	Buy Date	Maturity Date	31-03-16	31-03-17	31-03-18	31-03-19	31-03-20	31-03-21	31-03-22
2	12/09/17	15/09/20			15/09/17	15/09/18	15/09/19	15/09/20
3	12/09/17	05/09/20				05/09/18	05/09/19	05/09/20
4	10/09/17	25/10/20			25/10/17	25/10/18	25/10/19	25/10/20
5	10/09/17	05/10/20			05/10/17	05/10/19	05/10/19	05/10/20
6	18/09/17	12/08/20				12/08/18	12/08/19	12/08/20
7	18/09/17	22/08/20				22/08/18	22/08/19	22/08/20

<tbody>
</tbody>

Range C2:I7 should be filled up with formula.
Please help.
Thanking you all in advance

 

[oldbrewer]

C1 is clearly year to end of March 2016 - so what do you want in C2 - 15/09/15 ?

 

[Acekay]

C1 is clearly year to end of March 2016 - so what do you want in C2 - 15/09/15 ?

Sorry for the trouble. Row 1 is all year end.
In C2 cell it should be nil as buy date is 12/09/17 so for year end 31/03/2016 there will be no filling. As 12/09/17 is falling in year end 31/03/2018 it will reflect data from maturity column as 15th Sept but year will be 2017 so it should give result as 15/09/2017 in E2 & so on.

In 3rd row. 12/09/17 is buy date but as maturity day is prior i.e. 05/19 so it will not fall in 31/03/2018 but in 31/03/2019 and it will reflect 05/09/19 in E2 fatching day and month i.e.05th Sept from maturity column and year from column header i.e. 31/03/2019

Please write back if not clear.

Thanking you in advance.

 

[mail2master]

that's quite understood at first time as well... I have come up with a formula (actually a combination of many formule) but that looks clumsy.

=IF(AND(MONTH($C2)=MONTH($B2),DAY($C2)<DAY($B2),YEAR(D$1)>YEAR($B2)+1,YEAR(D$1)<=YEAR($C2)+1),DATE(YEAR($B2)+1,MONTH($C2),DAY($C2)),IF(AND(MONTH($C2)>=MONTH($B2),YEAR($C2)>=YEAR(D$1),YEAR($B2)<YEAR(D$1)),DATE(YEAR($B2),MONTH($C2),DAY($C2))))

Please note that it's not complete... Why am I posting this here because I don't want to proceed with this formula... I would prefer a neat formula.
If someone come up with a simple, short and neat formula, please let me know.

Acekay, you get the idea now... in case you want to proceed with this, go ahead.

 

[oldbrewer]

A

B

C

D

E

F

G

H

I

1

Buy Date

Maturity Date

31/03/2016

31/03/2017

31/03/2018

31/03/2019

31/03/2020

31/03/2021

31/03/2022

2

12/09/2017

15/09/2020

15/09/2017

15/09/2018

15/09/2019

15/09/2020

3

12/09/2017

05/09/2020

05/09/2018

05/09/2019

05/09/2020

4

10/09/2017

25/10/2020

25/10/2017

25/10/2018

25/10/2019

25/10/2020

5

10/09/2017

05/10/2020

05/10/2017

05/10/2019

05/10/2019

05/10/2020

6

18/09/2017

12/08/2020

12/08/2018

12/08/2019

12/08/2020

7

18/09/2017

22/08/2020

22/08/2018

22/08/2019

22/08/2020

col D

01/04/2015

01/04/2016

01/04/2017

01/04/2018

01/04/2019

01/04/2020

01/04/2021

1

Buy Date

Maturity Date

31/03/2016

31/03/2017

31/03/2018

31/03/2019

31/03/2020

31/03/2021

31/03/2022

2

12/09/2017

15/09/2020

15/09/18

15/09/19

15/09/20

15/09/21

row 15

3

12/09/2017

05/09/2020

05/09/18

05/09/19

05/09/20

05/09/21

4

10/09/2017

25/10/2020

25/10/18

25/10/19

25/10/20

25/10/21

5

10/09/2017

05/10/2020

05/10/18

05/10/19

05/10/20

05/10/21

6

18/09/2017

12/08/2020

12/08/18

12/08/19

12/08/20

12/08/21

7

18/09/2015

22/08/2018

22/08/16

22/08/17

22/08/18

22/08/19

I changed the last row of data for test purposes

D15 is

=IF(AND($B15<D$13,$C15<D$13),"",IF($B15>D$14,"",LEFT(TEXT($C15,"dd/mm/yy"),6)&RIGHT(TEXT(D$14,"dd/mm/yy"),2)))

dragged across and down

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Tetra201]

See if the following formula works for you:

=IF($A2 > IFERROR(EDATE($B2,-DATEDIF(EDATE(C$1+1,-12),$B2,"y")*12),0),"",IFERROR(EDATE($B2,-DATEDIF(EDATE(C$1+1,-12),$B2,"y")*12),""))

 

[Acekay]

See if the following formula works for you:

=IF($A2 > IFERROR(EDATE($B2,-DATEDIF(EDATE(C$1+1,-12),$B2,"y")*12),0),"",IFERROR(EDATE($B2,-DATEDIF(EDATE(C$1+1,-12),$B2,"y")*12),""))

superb. its working . Thanx a ton.

 

[Tetra201]

You are welcome.