Intro
Extract year and month from date in Excel with ease. Learn how to use formulas and functions to separate dates into year and month components. Discover techniques for date manipulation, including using YEAR, MONTH, and TEXT functions. Master Excel date extraction and enhance your data analysis skills with this step-by-step guide.
Extracting specific parts of a date in Excel, such as the year or month, is a common task that can be accomplished using various methods, including formulas and functions. Here's how you can extract the year and month from a date in Excel:
Extracting the Year
To extract the year from a date, you can use the YEAR function in Excel. This function takes a date as its argument and returns the year of that date.
Formula: =YEAR(A1)
- A1 is the cell containing the date from which you want to extract the year.
Example:
| Date | Year |
|---|---|
| 2023-02-15 | 2023 |
If the date is in cell A1, the formula =YEAR(A1) will return 2023.
Extracting the Month
To extract the month from a date, you can use the MONTH function in Excel. This function takes a date as its argument and returns the month of that date.
Formula: =MONTH(A1)
- A1 is the cell containing the date from which you want to extract the month.
Example:
| Date | Month |
|---|---|
| 2023-02-15 | 2 |
If the date is in cell A1, the formula =MONTH(A1) will return 2, which corresponds to February.
Displaying the Month as a Text String
If you want the month to be displayed as a text string (e.g., "February" instead of "2"), you can use the TEXT function in combination with the MONTH function.
Formula: =TEXT(A1,"MMMM")
- A1 is the cell containing the date.
Example:
| Date | Month |
|---|---|
| 2023-02-15 | February |
This formula will return the full month name.
Using the Format Cells Option
Another way to extract the year or month from a date is by using the Format Cells option. This method changes how the date is displayed without using a formula.
- Select the cell(s) containing the date(s).
- Right-click and select "Format Cells..."
- In the Format Cells dialog, click on the "Number" tab, then select "Custom" from the list on the left.
- In the "Type" field, you can enter a format code:
yyyywill display the year (e.g., 2023).mmmmwill display the full month name (e.g., February).mmmwill display the abbreviated month name (e.g., Feb).
- Click "OK" to apply the format.
Conclusion
Extracting the year or month from a date in Excel can be done using formulas with the YEAR and MONTH functions or by adjusting the cell's number format through the Format Cells option. Each method has its uses depending on whether you need the result as a value for further calculations or simply as a display format.