قالب وردپرس درنا توس
Home / Tips and Tricks / How to use the YEAR function in Microsoft Excel

How to use the YEAR function in Microsoft Excel

Excel logo on a gray background

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.

Make sure cells with dates in Excel are set to appropriate 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 =YEAR(CELL), replacing CELL with a cell reference. For example, =YEAR(A2) takes the date value from cell A2 and extracts the year from it.

Example of the YEAR function used in Microsoft Excel

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.

An Excel formula that uses the YEAR function, which extracts one year from a 5-digit serial number counted from January 1, 1900.

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.

An example DATE formula value, created using the value created by a YEAR formula in Excel

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

Source link