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.

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:

Step 1: In B2, enter the formula =YEAR(A2).

Extract Year from Date & Time Format 2

Step 2: 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.

Convert Date & Time to Date by Format Settings


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

Extract Year from Date & Time Format 4

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.

Extract Year from Date & Time Format 5

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

Extract Year from Date & Time Format 6

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)…

 

 

Related Posts

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Add Months To Date In Excel

It is important to adjust time periods when performing financial modeling. In Excel, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you ...

Add Days to Date in MS Excel

If you are an vavid MS Excel user, then you might have come across situations where you need to add the same or different days into the particular date, and if you have done this task manually, then let me ...

Filter Data By Date Field

The use of Microsoft Excel's Format Cells and Auto Filter option is a great way to filter data by year or Month. This article will discuss how to filter by date field using Filter function and Format cells in Microsoft ...

How to Count Dates of Given Year in Excel

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

Sidebar