Calculation of incremental date on completion of 18 years

[baidya91]

If joining date be between 2nd July to 31st December, the date of 18 years' increment will be 2nd July to 31st December after 18 years. But if the joining be between 1st January to 1st July, the date of 18 years' increment will be 1st July after 18 years. For Example, If A1 = 22/10/2000 as joining date, B1 = 22/10/2018 as the incremental date of 18 years. But
IF A1 = 03/02/2000 as the joining date, B1 = 01/07/2018 will be the incremental date of 18 years. How to put that in excel formula? Please help me. Thanks in advance.

 

[JackDanIce]

Try:

=DATE(YEAR(A1)+18,MONTH(A1)+6*--(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) < DATE(YEAR(TODAY()),7,2)),DAY(A1))

 

[bosco_yip]

B1, copied down :

=MAX(DATE(YEAR(A1)+18,MONTH(A1),DAY(A1)),DATE(YEAR(A1)+18,7,1))

Regards

 

[baidya91]

b1, copied down :

=max(date(year(a1)+18,month(a1),day(a1)),date(year(a1)+18,7,1))

regards

many many thanks. It works fine.