How to Extract Year from Date & Time Format in Excel

Sometimes we want to get Year information from date and time format to show the Year only in excel. For example convert 01/29/2019 06:51:03 to 2019, we can get Year information by Formula or Format Settings. The two ways are easy to learn, so you can select one you like to convert date and time format to Year only.

1. Convert Date & Time to Date by Formula

Prepare a table with date and time, the format is m/d/yyyy h:mm.

Extract Year from Date & Time Format 1

If we want to convert them to Year only, we can follow below steps:

Step1: In B2, enter the formula:

 =YEAR(A2)
Extract Year from Date & Time Format 2

Step2: Click Enter to get the result. Verify that format is changed to Year only properly, other part is cleared.

Extract Year from Date & Time Format 3

If there are other date and time listed under A2, user can drag the Fill Handle down to fill other cells.

2. Convert Date & Time to Date by Format Settings

You can use the “Format Cells” option to display only the year in a separate cell. Just do the following steps:

Step1: On A2, right click to load menu, select Format Cells.

Extract Year from Date & Time Format 4

Step2: In Format Cells window, under Number tab, in Category list, select Customer; then in Type textbox enter yyyy which is the Year format. Then proper Year like 2010 is displayed in Sample field.

Extract Year from Date & Time Format 5

Step3: Click OK. Verify that only year is show in cell.

Extract Year from Date & Time Format 6

This will display the year in a separate cell, formatted as a four-digit number.

3. Video: Extract Year from Date & Time Format in Excel

This video will demonstrate how to extract the year from date and time format in Excel using a formula and the format cell option.

4. Related Functions

  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…