Formula for changing Year on the basis of Month and Date

anshikam

Board Regular
Joined
Aug 20, 2016
Messages
87
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?
 

Some videos you may like

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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Aug 20, 2016
Messages
87
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
I'll leave that for a VB expert.........

Bump this if you get no response
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top