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.
Table of Contents
Convert Date & Time to Date by Formula
Prepare a table with date and time, the format is
If we want to convert them to Year only, we can follow below steps:
Step 1: In B2, enter the formula =YEAR(A2).
Step 2: Click Enter to get the result. Verify that format is changed to Year only properly, other part is cleared.
If there are other date and time listed under A2, user can drag the Fill Handle down to fill other cells.
Convert Date & Time to Date by Format Settings
Step 1: On A2, right click to load menu, select Format Cells.
Step 2: 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.
Step 3: Click OK. Verify that only year is show in cell.
- 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)…