Understanding Date Formats in Excel:
Before we dive into extracting the year from a date, let’s quickly review how Excel stores and interprets dates. In Excel, dates are stored as serial numbers, with January 1, 1900, being represented by the serial number 1. This means that each date corresponds to a unique serial number, making it easy for Excel to perform calculations and comparisons with dates.
Methods for Extracting the Year:
Excel offers several methods for extracting the year from a date, each with its own advantages and use cases. Let’s explore some of the most common methods:
Using the YEAR Function:
The simplest way to extract the year from a date in Excel is by using the YEAR function. This function takes a date as its argument and returns the year component of that date.
=YEAR(A1)
This formula extracts the year from the date in cell A1 and returns the result.
Using Date Formatting:
Another method for visually extracting the year from a date is by applying custom date formatting. Excel offers a variety of date formats, including formats that display only the year component of a date.
- Select the cell containing the date.
- Right-click and choose “Format Cells” from the context menu.
- In the Format Cells dialog box, select “Custom” from the Category list.
- Enter a date format that includes only the year component, such as “yyyy” or “YYYY-MM-DD”.
- Click “OK” to apply the formatting.
Excel will display the date with only the year component visible, making it easy to see at a glance.
Using Text Functions:
If the date is stored as text in a non-standard format, you can use text functions like LEFT, RIGHT, or MID to extract the year. For example, if the date is in the format “DD/MM/YYYY”, you can use the MID function to extract the year from the text.
=MID(A1,7,4)
This formula extracts the year from the date in cell A1, assuming it is in the format “DD/MM/YYYY”.