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 will give you the year value in a separate cell, allowing you to use it in a separate formula.

The YEAR function can be especially useful if you’ve converted text to date values in Excel, and you want to extract the year values from your data.

RELATED: How to Add Alternative Text to an Object in Microsoft Excel

To use the YEAR function, you’ll need to open your Excel spreadsheet and have cells containing dates (in any format) set as an appropriate “Date” number value.

It’s best to set these cell values as “Long Date” or “Short Date” numbers value using the Home > Number drop-down menu. You can also use cells with custom date formats.

Ensure cells with dates in Excel are set to an appropriate "Date" number value using the Home > Number drop-down menu.

If you have a date converted to a number, you can also extract the year from a 5-digit Excel “serial” number, which counts the number of days from the 1st January 1900. You can see this value by changing any date value to a standard number value using the Home > Number menu.

Because of this particular limit, you can only use YEAR to extract the year from dates starting from the 1st January 1900 onwards. The function won’t work with dates earlier than that.

To extract the year from a cell containing a date, type =YEAR(CELL), replacing CELL with a cell reference. For instance, =YEAR(A2) will take the date value from cell A2 and extract the year from it.

Examples of the YEAR function used in Microsoft Excel

The example above shows various styles of date values in column A. Regardless of the format, the YEAR function used in column B is able to read these and extract the year value.

Related :   Should I Leave My Laptop Plugged In All The Time?

If you prefer, you could also use a 5-digit Excel “serial” number, rather than a cell reference.

An Excel formula using the YEAR function, extracting a year from a 5-digit serial number counting from 1st January 1900.

The example above shows this using the formula =YEAR(43478), with a serial number (43478) matching the date (13th January 2019), which the YEAR function is able to understand. From this serial number, the year (2019) is returned.

The value returned by a formula containing the YEAR function can then be used by other formula. For instance, you could combine it with a DATE formula (eg. =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 into additional cells.

, , , , , , , , , , , , , , ,
Lucila is a freelance writer and lifelong learner with an ongoing curiosity to study new things. She enjoys checking out the latest grammar books and writing about video games more than anything else. If she's not running through Colorado’s breathtaking landscape, she's indoors hidden away in her cozy game room trolling noobs and leveling up an RPG character. She is a Final Fantasy IX apologist (although she loves them all… except XV), coffee aficionado, and a bit of a health nut. Lucila graduated from Western Kentucky University with a B.A. in English Literature with a minor in Creative Writing.

Leave a reply

Your email address will not be published. Required fields are marked *



Recent Comments