Checking for and incrementing a year in a date

[Evernight2021]

I have a column that contains mostly dates (and some column headers and blank spaces) in MM/DD/YYYY format. I want to automatically increment the year for all of the dates in this column.

I know that the following code increments the year of one cell:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))[/FONT]

However, how do I check the entire column, pick out just the dates and apply that formula to them?

 

[jtakw]

Hi,

If the column contains either a Date, Blank, or Headers (that are Text, or Alpha numeric):

Book1
	A	B
1	Header
2	12/28/2018	12/28/2019
3
4	Header 2
5	12/29/2018	12/29/2019
6
7	12/30/2018	12/30/2019
8
9
10	Header 3
11
12	12/31/2018	12/31/2019
Sheet431

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(A1),EDATE(A1,12),"")

I've used the EDATE function rather than the DATE function, you can use either.

B1 formula copied down.

 

[Evernight2021]

Is it possible to use something like that in VB to replace the current value with the incremented value?

 

[jtakw]

Yes, VBA can definitely do that, and you originally asked about a formula, I'm more of a formula guy, so I'm sure someone else can help you with doing that in VBA.

Have a nice day.

 

[Evernight2021]

My apologies for not being more clear. I'm looking to use a push button to increment the dates so I can control when they increment.

 

[jtakw]

I have an idea for you.

If you want to be able to control When and How Many years to Increase Or Decrease Column A (in my sample) years, you can consider this modification to my formula above.
In D2, enter the number of years you want increased, if you enter a negative number, say -2 (minus 2), the year will Decrease by 2 years, positive numbers will Increase the years accordingly, if left Blank or 0 (zero) entered, no change.

Book1
	A	B	C	D
1	Header			Years to Increse/Decrease
2	12/28/2018	12/28/2019		1
3
4	Header 2
5	12/29/2018	12/29/2019
6
7	12/30/2018	12/30/2019
8
9
10	Header 3
11
12	12/31/2018	12/31/2019
Sheet431

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(A1),EDATE(A1,D$2*12),"")

 

[Rick Rothstein]

Here is a macro for you to try...

[table="width: 500"]
[tr]
	[td]Sub IncreaseDatesByOneYear()
  Dim R As Long
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    If IsDate(Cells(R, "A").Value) Then Cells(R, "A").Value = DateAdd("yyyy", 1, Cells(R, "A").Value)
  Next
End Sub[/td]
[/tr]
[/table]

 

[Tetra201]

Or this one:

Sub Add_A_Year()
    Dim a As String
    a = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
    Range(a) = Evaluate("IF(ISNUMBER(" & a & "),EDATE(" & a & "+0,12)," & a & "&"""")")
End Sub

 

[Evernight2021]

Or this one:

Sub Add_A_Year()
    Dim a As String
    a = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
    Range(a) = Evaluate("IF(ISNUMBER(" & a & "),EDATE(" & a & "+0,12)," & a & "&"""")")
End Sub

This worked perfectly! Thank you!

 

[Tetra201]

You are welcome.

Happy New Year!