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
1. Convert Date & Time to Date by Formula
Prepare a table with date and time, the format is m/d/yyyy h:mm.
If we want to convert them to Year only, we can follow below steps:
Step1: In B2, enter the formula:
=YEAR(A2)
Step2: 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.
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.
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.
Step3: Click OK. Verify that only year is show in cell.
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)…