To get future date

[Muthukrishnan V]

Excel 2007.

Col A11 to A40 contain names.
Col B11 to B40 contain date of birth (dd-mm-yyyy)


In Col A1 I enter a date, say 10-08-2018 (dd-mm-yyyy) or =today().



My request: Col C11 to C40 to show next occurrence of birthday
anniversary based on the date entered in A1.

To illustrate, if B11 is 13-01-1949, next birth anniversary is 13-01-2019. (next occurrence based on A1)
C11 to show 13-01-2019. Request formula.

 

[Joe4]

Here is one way.

Place this formula in C11 and copy down for all rows:

=IF(DATE(YEAR(TODAY()),MONTH(B11),DAY(B11))>$A$1,DATE(YEAR(TODAY()),MONTH(B11),DAY(B11)),DATE(YEAR(TODAY())+1,MONTH(B11),DAY(B11)))

 

[Peter_SSs]

You could also try this in C11, copied down.

=EDATE(B11,DATEDIF(B11,A$1,"y")*12+12)