Do you need to extract years from dates in Excel for data preprocessing or feature engineering? You’ve come to the right place. Read this Excel tutorial until the end to find the best methods to extract the year component from raw data with step-by-step instructions and visual illustrations.

Microsoft Excel offers many tools and functions to structure, analyze, and visualize your data in many ways. One such way is analyzing and visualizing raw data from the year perspective.

It could be for trend identification, time series analysis, or just to hide personally identifiable information from raw data.

Excel provides you with built-in functions and cell formatting to quickly extract years from a small dataset. If the source data is large, you can make use of programmatic tools to automate the task.

In this article, I’ll show five different ways to extract year entries from various date entries like Short Date and Long Date in your Excel worksheet.

Reasons to Extract Year From Date in Excel

The primary reason to learn how to extract years from date entries in Excel is automation. Imagine you’ve got a dataset of candidates containing date of birth information. But you don’t want to disclose that to employees or contractors who might work on the dataset.

Do you create a new column, manually enter the years, and then hide the original date of birth column? Of course not! Being an Excel champ, you must learn to use formulas or dynamic Excel tools.

Here are the situations when you need to extract the year from the date in your dataset:

1. Extracting years from dates is crucial for trend analysis. It enables you to understand patterns and trends over specific time periods.
2. In finance, extracting years from dates is essential for generating annual reports, tracking fiscal performance, and meeting regulatory reporting requirements.
3. Time-series forecasting models often rely on yearly trends. The extraction of years is a vital preprocessing step that enhances the accuracy of predictions.
4. When segmenting data for analysis, extracting years facilitates the grouping of information into meaningful time intervals. Then, you can easily compare performance, sales figures, etc.
5. In event-driven analyses, extracting years helps identify patterns or correlations related to specific years. For instance, the impact of economic events or marketing campaigns year on year.
6. In demographic analyses, you need to extract the year of birth from the DOB to calculate the age of the people involved in the study.
7. Analyzing website traffic data involves extracting years from timestamps to evaluate annual user engagement trends.

Now, let us explore various methods of extracting the year component from the date entries below:

Extract the Year with the YEAR Function

The easiest method to extract the year of a short or long date in Excel is the YEAR function.

It’s also an accessible function because Microsoft included it in the Excel 2007 desktop app and since then it has been available in all Excel editions, including Mac and Excel for the web.

Find below the steps to use this function:

1. Select a cell where you want to extract the year value from a short or long date.
2. Enter the following formula into the cell and hit Enter:

=YEAR(B2)

3. Excel will extract the year from the date in the adjacent cell B2.
4. Use the fill handle of the selected cell and drag it down the column to apply the formula to the required cells in your worksheet.
5. Excel will keep extracting the year values as you drag the fill handle down the column.

In the above formula, B2 is the source cell for the date value. You can change it to any other cell reference according to your dataset.

Extract the Year with the TEXT Function

You mostly use the TEXT function to modify how Excel visualizes a numerical value in one or many cells. Did you know you can also use the same function to extract the year component from a date value? That’s right, you can use the TEXT function as well.

Find below the steps to follow:

1. Highlight the cell next to a date value.
2. Enter the following formula into the cell:

=TEXT(B2,"yyyy")

3. Hit Enter.
4. You should see the year component in the cell.
5. Copy and paste the formula down the column or use the fill handle.
6. Excel shall apply the formula and extract the year components for the rest of the cells.

Get the Year with the Excel Format Cells Dialog Box

Format Cells dialog enables you to decide how the content of a cell will show up on your worksheet. By entering a custom cell formatting, you can restrict the month and date components of any short and long dates. Therefore, the cell will only display the year component.

This method is particularly helpful when you want to automatically hide the full date and only show the year part. For example, your mobile app or website collects personally identifiable information of visitors through surveys or forms. These data could be DOB, date of purchase, date of registration, date of solemnization, and so on.

You don’t want to reveal the full date to someone else working on the dataset. In this case, follow these steps to customize the cell formatting of a cell range so Excel will only show the year component:

1. Highlight the cell range containing short or long dates.
2. Alternatively, highlight a blank cell range where collected date data will populate from apps or websites.
3. Press Ctrl + 1 to bring up the Format Cells dialog.
4. Select the Custom category on the left side panel.
5. Enter four or two Ys inside the Type field.
6. Click OK.

7. Excel will extract the year values from the source dates.
8. If you’re entering new dates, Excel will only show the year components for the formatted cells.

Extract the Year Using Power Query

When exporting a large dataset to an Excel worksheet and you also need the year components from this dataset as a new column, you should use Power Query to transform the imported data.

You can use Power Query to import datasets to Excel from various sources. For example, there are options like SQL server database, Azure, Power BI, Dataverse, SharePoint Online, and many more.

For third-party data imports, you need to go to the Data tab and click the Get Data command button. It’ll open a context menu for the available data sources.

Or, you can also export the existing Excel worksheet dataset to Power Query, transform the data the way you want, and export it back to the worksheet. For this, you must use the From Table/Range command in the Data tab.

There are two ways to extract years from dates. You can create a new column and use a Power Query M formula: Date.Year([source column]). Or, you can use the Date > Year function in the Transform tab of Power Query.

I’m showing below how to use both of these methods in easy steps:

Extract Year From Date Using a Custom Column

Highlight the data column you want to export to Power Query. then, click the Data tab on the top and click on the From Table/Range button.

Click OK on the Create Table dialog. The My table has headers checkbox should be checkmarked by default.

You should now see the Power Query Editor tool. Go to the Add Column tab and choose the Custom Column option.

On the Custom Column dialog, enter a new column name. It’ll show up on the Power Query Editor and Excel worksheet. Also, add the following formula inside the formula field of the Custom Column dialog.

Date.Year([Date of Birth])

In the above formula, you need to change the [Date of Birth] entry to a different one according to your dataset. For example, if the date column header is Dates, the formula will change to the following:

Date.Year([Dates])

Click OK on the Custom Column dialog. The Power Query tool will create a new column as you named it above, extract the year values, and paste it into the newly created column.

Now, click the File tab and choose the Close & Load To option.

On the Import Data dialog, select the Existing worksheet option and highlight a cell range as the destination for the Power Query data.

Click OK on the Import Data dialog and you should instantly get your year values as well as the source dates in your worksheet. I deleted the Date of Birth column (date source) so the dataset doesn’t contain any redundant columns.

Extract Year From Date Using the Date Tool

You should be able to import your dataset to the Power Query Editor by following the instructions mentioned earlier. Then, follow these steps:

1. Click the Transform tab.
2. Click the Date drop-down and hover the cursor over the Year option.
3. Click on the Year option in the overflow menu.

4. Power Query will transform the existing short or long dates to year values.

You can now use the Close & Load To command to export the transformed dataset to your Excel worksheet.

Extract the Year with Using Excel VBA

Excel VBA offers you unmatched automation when you’re extracting thousands of year values from a large dataset. Feel free to use the following VBA scripts. I’ve also added the steps to refresh your memory on how to use the VBA code you find online.

Extracting Years From Dates in Any Cell

The following code will look for all the date entries in your worksheet. Then, it’ll extract years from those entries and paste the result into an adjacent cell. If there aren’t any blank cells, Excel VBA will create one and then print the year value.

Sub ExtractYearsAnyCells()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim extractedYear As Variant
    Dim targetCell As Range
    
    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet6")
    
    ' Set the range to search for dates (adjust as needed)
    Set rng = ws.UsedRange
    
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell contains a date
        If IsDate(cell.Value) Then
            ' Extract the year
            extractedYear = Year(cell.Value)
            
            ' Set the target cell to the cell next to the current cell
            Set targetCell = cell.Offset(0, 1)
            
            ' Check if the target cell is not empty
            If targetCell.Value <> "" Then
                ' Insert a new cell to the right
                targetCell.Offset(0, 1).Insert Shift:=xlToRight
                ' Move to the newly inserted cell
                Set targetCell = targetCell.Offset(0, 1)
            End If
            
            ' Place the extracted year in the target cell
            targetCell.Value = extractedYear
        End If
    Next cell
End Sub

Follow the comments mentioned in the above script to customize any code element, if required.

To create a macro using the above VBA script, follow these steps:

1. Press Alt + F11 to call the Excel VBA Editor.
2. Click the Insert button and choose Module.
3. Copy and paste the above script into the blank module.
4. Click Save and follow onscreen instructions to save the workbook as an XLSM (Macro-enabled) file.
5. Close Excel VBA.

6. Press Alt + F8 to open the Macro dialog box.
7. Choose the ExtractYearsAnyCells macro and hit Run.
8. Excel will automatically find dates and extract year values from those dates.

Extracting Years From Dates in a Cell Range

If you need to tell Excel VBA to perform year value extraction in a selected cell range and populate the results in the adjacent column, use this script:

Sub ExtractYearsDefinedRange()
    Dim ws As Worksheet
    Dim rngDates As Range
    Dim cell As Range
    Dim extractedYear As Variant    
    Set ws = ThisWorkbook.Sheets("Sheet1")    
    Set rngDates = ws.Range("B2:B100")    
    For Each cell In rngDates
        If IsDate(cell.Value) Then
            extractedYear = Year(cell.Value)            
            cell.Offset(0, 1).Value = extractedYear
        End If
    Next cell
End Sub

In this script, modify the following to customize the macro:

• Replace the Sheet1 code element with the exact worksheet name.
• Change the reference of cell range B2:B100 to another range according to your worksheet.

Conclusions

Knowing the tricks to extract the year from the date in Excel is a valuable skill if you’re into data-driven decision-making.

Here you learned different methods for different cases of extracting year from date. If you’re doing this occasionally on a small dataset, you can use the YEAR and TEXT functions of the Excel software.

Suppose, you want to create a data-entry worksheet where all short and long date entries shall only show the year component, you can use the Format Cells tool of Excel.

Finally, if you’re looking for time-saving and automation, you can use programmatic methods like Excel VBA and Power Query.

Which one did you like the most? Do you know a better solution? Feel free to share your experiences, questions, or additional insights in the comment box below.