If you need to quickly extract the year from a date in Microsoft Excel, you can use the YEAR function. This gives you the annual value in a separate cell, so you can use it in a separate formula.
The YEAR function can be especially useful if you have converted text to date values in Excel and want to extract the annual values from your data.
RELATED: How to convert text to date values in Microsoft Excel
To use the YEAR function, you must open your Excel spreadsheet and set cells that contain dates (in any format) as an appropriate “Date”; numeric value.
It is best to set these cell values as “Long Date” or “Short Date” values using the Home> Number drop-down menu. You can also use cells with custom date formats.
The drop-down list for numbers. “Width =” 426 “height =” 175 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “onerror =” this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); “>
If you have a date converted to a number, you can also extract the year from a 5-digit “serial number” in Excel, which counts the number of days from January 1, 1900. You can see this value by changing any date value to a default number value with using the Home> Numbers menu.
Due to this special limit, you can only use YEAR to extract the year from the date of January 1, 1900 onwards. The function does not work with dates earlier than that.
To extract the year from a cell that contains a date, type
CELL with a cell reference. For example,
=YEAR(A2) takes the date value from cell A2 and extracts the year from it.
The example above shows different styles of date values in column A. Regardless of the format, the YEAR function used in column B can read these and extract the annual value.
If you prefer, you can also use a 5-digit Excel “serial number” rather than a cell reference.
The example above shows this using the formula
=YEAR(43478), with a serial number (43478) that matches the date (January 13, 2019), which the YEAR function can understand. From this serial number, the year (2019) is returned.
The value returned by a formula that contains the YEAR function can then be used by another formula. For example, you can combine it with a DATE formula (e.g.
=DATE(YEAR(A2),1,11 ) to create a valid date value.
If you want to repeat it for multiple date values, you can use the fill handle to copy the YEAR formula to additional cells.
RELATED: How to automatically fill sequential data in Excel with Fill Handle