addding a year to a date

Question

I am looking to take a date in a chart and automatically add 1 year to the date. I want to be able to enter the date of the certificate in cell a1 and once I hit enter I want the macro to rewrite the date 1 year from that current date. I want the outcome to remain in cell A1

hansvogelaar · Answer

christopher_Eade&nbsp;
You added Excel for Web as tag. VBA macros don't work there, only in the desktop version of Excel for Windows/Mac.
You could do the following on Windows/Mac:
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        If IsDate(Range("A1").Value) Then
            Application.EnableEvents = False
            Range("A1").Value = DateAdd("yyyy", 1, Range("A1").Value)
            Application.EnableEvents = True
        End If
    End If
End Sub

Forum Discussion

addding a year to a date

1 Reply