Excel VBA: Extract the Year from a Date in a Worksheet

VBA & macros · Updated November 21, 2026

To extract a year from a date in Excel VBA, use the built-in Year function: pass it a valid date value, and it returns the year as a number. For a worksheet column, read each date cell, apply Year(CDate(value)), and write the result to another column; the example below handles blanks and invalid entries as well.

The Year function in VBA

Year is a VBA date function that returns an integer from 1 to 9999 for a valid date. The key is to give it a value VBA recognizes as a date. If the date is in a worksheet cell, VBA reads the cell’s underlying value, which is generally an Excel date serial rather than the formatted text you see on screen. Converting that value with CDate makes the intended date conversion explicit.

For example, if cell A2 contains the actual date June 18, 2024, using Year on that cell’s value returns 2024. The day and month do not affect the result. The function extracts the calendar year; it does not calculate a fiscal year, determine a week number, or change the original date.

VBA’s Year function is different from a worksheet formula only in where and how you use it. A VBA macro can process many rows, put results in another column, or use the year in a decision. A worksheet formula is often simpler when you want results to update automatically as dates change.

Extract a year from one cell

For a single date, assign the result of Year to a variable or use it directly in your VBA logic. The following is the basic pattern, shown in context as a small macro. It checks whether the active cell contains a date before attempting the conversion:

Sub ShowYearFromActiveCell()
    Dim dateValue As Variant
    Dim extractedYear As Long

    ' Read the selected cell and validate its value.
    dateValue = ActiveCell.Value
    If IsError(dateValue) Then
        MsgBox "The selected cell contains an error."
    ElseIf IsDate(dateValue) Then
        extractedYear = Year(CDate(dateValue))
        MsgBox extractedYear
    Else
        MsgBox "Select a cell containing a valid date."
    End If
End Sub

This example displays the year in a message box; it does not change the worksheet. The variable is declared as Long, which is suitable for storing the returned year, while Variant lets the input hold different cell value types before validation. If your macro already has a validated VBA Date value, you can pass that value directly to Year.

Extract years for a worksheet column

To process a list, put dates in column A with a header in A1, then run the macro below. It writes the year to column B on the same row, leaves blank input rows blank, and marks errors or unrecognized values as Check date. The macro finds the final used row in column A, so it does not need a fixed row count.

Save a copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z, so a backup is the simplest way to preserve the original if the output is not what you intended.

Sub ExtractYearsToColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim dateValue As Variant

    ' Use the active sheet; dates are in A and results go in B.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Cells(1, 2).Value = "Year"

    For rowNum = 2 To lastRow
        dateValue = ws.Cells(rowNum, 1).Value
        If IsError(dateValue) Then
            ws.Cells(rowNum, 2).Value = "Check date"
        ElseIf Len(Trim$(CStr(dateValue))) = 0 Then
            ws.Cells(rowNum, 2).ClearContents
        ElseIf IsDate(dateValue) Then
            ws.Cells(rowNum, 2).Value = Year(CDate(dateValue))
        Else
            ws.Cells(rowNum, 2).Value = "Check date"
        End If
    Next rowNum
End Sub

The macro places numbers, not text, in column B when it succeeds. If those results appear as dates instead of years, the destination cells may have a date number format. Select the output cells and choose Home > Number Format, then select General or Number. The source dates remain unchanged because the macro writes only to column B.

Using a worksheet formula instead

If you do not need VBA, Excel’s worksheet YEAR function is usually the more direct choice. With dates in A2:A20 and the year result for each date going in B2:B20, enter =YEAR(A2) in B2, then fill it down. For a date of June 18, 2024 in A2, the formula returns 2024. A formula recalculates when the source date changes; a macro’s written value does not update unless you run the macro again.

Choose the VBA approach when the year is part of a larger automated task, such as sorting records into year-specific groups or preparing a report. Choose a formula when you want a visible, continuously updating result beside each date. You can also use both: a formula for checking results, followed by a macro that copies and pastes values if fixed outputs are needed.

Dates, serial numbers, and regional settings

Excel stores ordinary dates as serial numbers and uses cell formatting to display them as dates. For example, a cell may show 6/18/2024 while its underlying value is numeric. VBA can extract the year from that underlying date value even though the displayed appearance can vary by number format. Changing a cell’s display format alone does not convert text into a real date.

Text that looks like a date needs extra care. IsDate and CDate interpret date strings according to the computer’s regional settings. A string such as 04/05/2024 can mean April 5 or May 4, depending on the locale. If data arrives from another system, prefer real Excel date values or use a clearly specified input format and parse it deliberately rather than trusting an ambiguous string.

Excel workbooks can use either the 1900 or 1904 date system, and the same serial number can represent different calendar dates under those systems. Normally, VBA reads a date cell using the workbook’s date context, so using the cell’s value is safer than treating a serial number as a year or hard-coding a serial-to-date calculation. Be especially careful when copying raw date serials between workbooks configured with different date systems.

Common problems and how to avoid them

  • The result is a date instead of a year: The output cell may have a date format. Apply General or Number formatting to the result cells; the value returned by Year is still a number.
  • The macro reports invalid dates: The cell might contain text, an error value, or an ambiguous date string. Check the source data and confirm that dates are stored as actual dates.
  • Only some rows are processed: The sample macro finds the last used row in column A. If your dates are in another column, update the column references consistently, or use a different method to determine the final row.
  • The wrong year appears: The input might have been interpreted under a different regional date convention, or the source is not the date you expected. Inspect the full date value and its source rather than relying only on the displayed year.
  • Old results remain after editing the list: A macro writes fixed values and does not recalculate automatically. Run it again after changing the source dates, or use worksheet formulas for continuously updating results.

One common misconception is that changing a cell’s number format will make the year extraction work. Formatting affects display, not the underlying value. If a cell contains text such as an imported date string, first convert or parse that text as a date; simply formatting it as a date does not reliably repair it.

Run the macro in Excel for Microsoft 365

On Windows desktop Excel, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code into the module, and run the macro with F5 while the cursor is inside it. Another option is to return to Excel, press Alt+F8, select the macro, and choose Run. Make sure the worksheet with the dates is active, because the sample uses ActiveSheet.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the VBA code. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for these steps. On Mac, use the Developer tab’s Visual Basic command to open the editor, or the available Visual Basic Editor shortcut on your keyboard. Mac shortcut behavior can depend on the keyboard and function-key settings.

Only enable macros in files you trust. Do not lower security settings simply to run code from an unknown workbook. If Excel blocks a macro in a trusted file, follow your organization’s security guidance or check Excel’s macro security settings rather than enabling all macros indiscriminately.

When to use another approach

For one or a few cells, a worksheet formula is often easier to inspect and maintain. For repeated transformation of imported data, Power Query may be a better fit because it can apply a date transformation as part of a refreshable data-cleaning process. For a macro that already loops over rows, VBA’s Year function is concise and keeps the year extraction alongside the rest of the automation.

Also consider whether you want a calendar year or a fiscal year. Year always returns the calendar year represented by the date. A fiscal year that starts in a month other than January requires additional logic based on the organization’s chosen start month; changing the date format will not make Year return a fiscal-year label.

Frequently Asked Questions

What is the VBA function to get the year from a date?
Use Year(dateValue). For a worksheet cell, validate the value and, where appropriate, use Year(CDate(cell.Value)). The result is the calendar year as a number.

How do I extract the year from every date in a column?
Loop through the populated rows, read each cell’s value, check it with IsDate, and write Year of the date to a result column. The sample macro above uses dates in column A and outputs years in column B.

Does Year work with a date stored as text?
It may work if VBA recognizes the text as a date, but the interpretation depends on regional settings. Convert data to real dates or parse a known, unambiguous format before using the result in important work.

Why does my extracted year display as a date?
Excel may be displaying the numeric year using a date number format. Change the output cells to General or Number. This changes how the value appears, not the underlying year value.

Can I use the VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; a worksheet formula such as =YEAR(A2) is an alternative that works without a macro.

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.