# Formula for changing Year on the basis of Month and Date

#### anshikam

##### Board Regular
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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### gaz_chops

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
I'll leave that for a VB expert.........

Bump this if you get no response

#### Peter_SSs

##### MrExcel MVP, Moderator
See if this does what you want

VBA Code:
``MsgBox DateSerial(Year(Date) - (DateSerial(Year(Date), Month(DOB), Day(DOB)) < Date), Month(DOB), Day(DOB))``

Replies
2
Views
194
Replies
6
Views
260
Replies
4
Views
73
Replies
1
Views
172
Replies
3
Views
70

1,128,077
Messages
5,628,507
Members
416,323
Latest member
65563

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back