How to Convert Date to Year in Excel (3 Methods)

Dataset Overview

We’ll use the list of dates below to convert to year.

3 Quick Ways to Convert Date to Year in Excel


Method 1 – Convert Date to Year Using Excel YEAR Function

  • Open your Excel workbook.
  • In cell C5 (or any other cell where you want the year to appear), type the following formula:
=YEAR(B5)
  • Press Enter.

"</p

  • The result will be the year corresponding to the date in cell B5.

"</p

  • To get the year for the rest of the dates, use the Fill Handle (+) tool by dragging it down from cell C5.

Read More: How to Convert Date to Day of Year in Excel


Method 2 – Excel TEXT Function to Extract Year from a Date

  • In cell C5 (or any other cell), insert the following formula:

=TEXT(B5, "yyyy")

Excel TEXT Function to Extract Year from a Date

  • Press Enter.
  • Excel will return the year for the date in cell B5.
  • Similar to the previous method, use the Fill Handle to extract the year for all other dates.

Read More: How to Convert Date to Day of Week in Excel


Method 3 – Use Format Cells Option to Convert Date to Year in Excel

  • Select the entire dataset containing dates (e.g., B5:B10).
  • Right-click on the selection and choose Format Cells.

Use ‘Format Cells’ Option to Convert Date to Year in Excel 

  • In the Format Cells dialog box:
    • Go to the Number tab.
    • Click on the Custom category.
    • In the Type field, enter yyy (without quotes).
    • Press OK.

Use ‘Format Cells’ Option to Convert Date to Year in Excel 

  • All selected dates will be converted to years.

Use ‘Format Cells’ Option to Convert Date to Year in Excel 

Note: You can access the Format Cells dialog by going to Home, selecting the Number group and clicking on the More icon.

Read More: How to Convert Date to Month and Year in Excel


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Date Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo