Excel VBA Get Year From Date: Code, Dates, and Errors

VBA & macros · Updated November 21, 2026

To get the year from a date in Excel VBA, use the built-in Year function: Year(dateValue). For example, Year(Worksheets("Sheet1").Range("A2").Value) returns the year from the date in cell A2; assign that result to another cell or variable to use it.

How VBA’s Year function works

Year is a VBA date function that returns an integer from 100 through 9999 for a valid date. If the value represents June 15, 2024, the function returns 2024. It does not return a formatted date or the last two digits; the result is the full year number.

A date displayed in an Excel cell is generally stored as a serial number, with its appearance controlled by the cell’s number format. When a cell contains a real Excel date, VBA can read its value and pass it to Year. The value in the cell—not how many characters appear on screen—is what matters. For instance, a date displayed as “Jun-24” can still provide the year 2024.

Here is a small macro that reads A2 on Sheet1 and writes the year to B2:

Sub WriteYearFromA2()
    ' Read a date and place its year in the next column.
    Dim sourceDate As Variant
    Dim yearNumber As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    sourceDate = ws.Range("A2").Value

    If IsError(sourceDate) Then
        ws.Range("B2").Value = "Cell contains an error"
    ElseIf IsDate(sourceDate) Then
        yearNumber = Year(CDate(sourceDate))
        ws.Range("B2").Value = yearNumber
    Else
        ws.Range("B2").Value = "Not a valid date"
    End If
End Sub

The checks make the example safer than calling Year on any cell without examining its contents. The macro writes a message for an error value or invalid entry rather than stopping with a type mismatch. It uses CDate after the check to explicitly convert a date-like value to VBA’s Date type.

Extracting years for a whole column

For a list of dates, use a loop. This example reads A2:A100 and writes corresponding years to B2:B100. Empty cells and invalid entries are left blank, while Excel error values are labeled. Change the worksheet name and range to fit the workbook.

Sub WriteYearsForDateList()
    ' Copy years from column A to column B.
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim sourceDate As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    For rowNumber = 2 To 100
        sourceDate = ws.Cells(rowNumber, "A").Value

        If IsError(sourceDate) Then
            ws.Cells(rowNumber, "B").Value = "Cell error"
        ElseIf Len(CStr(sourceDate)) = 0 Then
            ws.Cells(rowNumber, "B").ClearContents
        ElseIf IsDate(sourceDate) Then
            ws.Cells(rowNumber, "B").Value = Year(CDate(sourceDate))
        Else
            ws.Cells(rowNumber, "B").Value = "Check date"
        End If
    Next rowNumber
End Sub

The loop’s bounds are fixed at rows 2 to 100, which is useful for a known-size list but will not automatically include new rows. To find the last used row in column A instead, replace the fixed upper bound with lastRow and calculate it using ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Declare lastRow as a Long. If the column has no data below the header, handle that case before looping so the macro does not treat the header as a date.

Putting the result in a variable or function

If the year is needed for a later calculation, store it in a Long variable rather than writing it to a worksheet. For example, within a procedure that already has a valid date variable named invoiceDate, the assignment is yearNumber = Year(invoiceDate). The variable holding the date should be declared as Date if the procedure controls the input, or as Variant if it needs to accommodate different cell contents before validation.

You can also create a reusable VBA function for use in other VBA procedures. This version accepts a Date and returns its year:

Function GetYearFromDate(ByVal dateValue As Date) As Long
    ' Return the four-digit year component.
    GetYearFromDate = Year(dateValue)
End Function

Because the argument is declared as Date, callers must supply a date VBA can convert to that type. If the original value might be blank, an error, or arbitrary text, validate it before calling the function. A worksheet cell can also use this function if it is in a standard VBA module and macros are enabled, but a worksheet formula is usually simpler for a result that should update automatically.

VBA Year versus the worksheet YEAR formula

VBA code and worksheet formulas use similar names, but they run in different places. In VBA, use Year(dateValue). In a worksheet, with a valid Excel date in A2, enter =YEAR(A2) in another cell. The formula recalculates when the source date changes, while a macro writes a value at the time it runs unless you build additional automation.

Choose a worksheet formula when you want a visible, automatically updating result beside each date. Choose VBA when the year extraction is one step in a larger task, such as importing records, generating a report, or copying selected results as fixed values. You can also have VBA write a formula, but that is not necessary if the requirement is simply to extract a year in a worksheet.

  • For a small table that should recalculate, use =YEAR(A2) and fill the formula down.
  • For a one-time transformation or multi-step process, use the VBA Year function in a macro.
  • For a reusable spreadsheet calculation, consider a worksheet formula or a custom VBA function, depending on whether macros are acceptable for the workbook’s users.

Dates, text, blanks, and regional formats

The most important distinction is between a true date value and text that only looks like a date. Excel may store an entry such as 6/15/2024 as a date serial, but a value imported from a text file may remain text. Formatting text to look like a date does not necessarily turn it into a date value. The IsDate check in the examples helps, but it should not be treated as a universal guarantee that ambiguous text means the intended calendar date.

Date strings can be interpreted according to regional settings. For example, “03/04/2024” can mean March 4 or April 3 depending on the convention used by the system or the conversion context. For reliable imports, prefer unambiguous source data and convert it deliberately. When constructing a date from separate numeric year, month, and day values, use VBA’s DateSerial function, such as DateSerial(2024, 6, 15), then pass the result to Year. It returns a Date for June 15, 2024, whose year is 2024.

Blank cells need an explicit policy. Depending on the value and conversion, passing an empty or unexpected value directly to a date function can produce an error or a misleading result. Decide whether blanks should remain blank, receive a label, or be treated as invalid. The loop example checks for an empty value before calling IsDate and Year. Also check for cell errors first: a value such as #N/A is not ordinary text, and attempting a normal conversion on it can cause a VBA error.

Common problems and fixes

  • Type mismatch: The cell may contain text, an error value, or another value that cannot be converted to a date. Test with IsError and IsDate before calling Year.
  • Wrong year from an ambiguous date: The source may be text interpreted using a different regional date order. Inspect the underlying value and confirm the intended month and day rather than relying on the displayed string.
  • The result appears as a date: The destination cell may have a date number format. The returned value is a year number, so format the output cell as General or Number if it should display as a plain integer.
  • The macro reads the wrong workbook or sheet: Qualify worksheet references, as the examples do with ThisWorkbook.Worksheets("Sheet1"). Unqualified Range references can point at the active sheet, which may not be the intended one.
  • The result does not update after changing the date: A macro writes a result when it runs; it is not automatically recalculated like a worksheet formula. Run the macro again, use a formula, or deliberately add event-driven VBA if automatic behavior is required.

For large data sets, repeatedly accessing individual worksheet cells can be slower than reading a range into an array, processing the values in memory, and writing the results back in one operation. For a short list, the straightforward loop is easier to understand and maintain. Optimize only when workbook size or run time makes the simpler approach inadequate.

Running and saving the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On Mac, the equivalent is commonly Option+F11; some keyboards require Fn+Option+F11. You can also open the editor from the Developer tab’s Visual Basic command. In the editor, choose Insert > Module, paste the procedure into the module, and run it from the editor or Excel’s macro list.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon on Windows and select Developer. The location and keyboard behavior can differ on Mac, but the VBA editor is available in desktop Excel for Mac. Excel for the web does not run VBA macros; open the workbook in the desktop application to use this code.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) so the macro can be retained. Before running a macro that writes into cells, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, and a macro may overwrite existing results if its output range is not chosen carefully. Enable macros only for workbooks and code you trust.

Frequently Asked Questions

What VBA code gets the year from a date in A1?
Use Year(Range("A1").Value) in a VBA procedure when A1 contains a valid date. For a safer macro, qualify the reference with a worksheet and check that the cell does not contain an error or invalid text first.

Does VBA Year return the last two digits or the full year?
It returns the full year as an integer. A date in 2024 returns 2024, not 24. If you need a two-digit display, format or transform the result deliberately rather than assuming Year truncates it.

Can I use Year to get the year from a date stored as text?
Sometimes, if VBA can interpret the text as a date, but ambiguous strings may be read differently under different regional settings. Validate and convert imported text carefully; changing the cell’s number format alone does not convert text into a real date.

Why does Year return an error for one row?
That row may contain a blank, an Excel error, or text that VBA cannot parse as a date. Check the value in that row and use IsError and IsDate before calling Year. A visibly date-like value is not always stored as a date.

Should I use VBA Year or the Excel YEAR formula?
Use the worksheet formula =YEAR(A2) when you want a result that updates as the source cell changes. Use VBA’s Year function when extracting the year is part of a macro or other VBA procedure.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.