Formula for changing Year on the basis of Month and Date

[anshikam]

Hello,

I have a requirement where i need a formula that would take a date(DOB) see if only the month and date is less than current date if yes then change the date with same month and date but year would be next year so + 365 days. If date and month are more than current date and month then year would be current year.
Eg:
1. Date is 01.01.1999 then resultant date should be 01.01.2021 since 1st Jan is less than current date and month.
2. Date is 01.11.1999 then resultant date would be 01.11.2020 since 1st Nov is greater than todays date and month.

I am using a variable(DOB) in vba to store the date. This is a date type variable.
It is being read from an excel file.

Any short way of doing this vs creating an entire script?

 

[gaz_chops]

Try

=IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY(),DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)),DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)))

A2 contains the D.O.B.

 

[anshikam]

Try

=IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY(),DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)),DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)))

A2 contains the D.O.B.

Doing this in a VBA Script so can i use a VBA variable value that stores the date i need instead of A2?

 

[gaz_chops]

I'll leave that for a VB expert.........

Bump this if you get no response

 

[Peter_SSs]

See if this does what you want

MsgBox DateSerial(Year(Date) - (DateSerial(Year(Date), Month(DOB), Day(DOB)) < Date), Month(DOB), Day(DOB))