In this tutorial, you will find how to get the month and year from a date in Excel. For certain reports, comparative data, and monthly analysis, the dates may hold little to no relevance but if you have the data with full dates, you may want to make it more relevant by only having the months and years as part of the dataset. We will achieve this using date formats, the TEXT function, and the MONTH and YEAR functions (joining their individual results using the CONCAT function).
Some features and functions may require you to enter the format codes for the months and years. Details on that are below:
Method #1 – Using the TEXT Function
Firstly, we are going to use the TEXT function to convert the dates. The TEXT function converts a value to text in a specific number format. We will use the TEXT function to extract the month and year components from the dates and convert them into the specified format.
Dates cannot be directly fed in the TEXT function therefore we need to supply it with a cell reference which means we need to keep the original data initially to feed the dates into the formula. Later you may copy and paste the values on themselves or over the original data and delete the extra column.
Use the following formula involving the TEXT function to extract the month and year from a date:
=TEXT(B3,"mmm/yy")The TEXT function takes the date in cell B3 and converts it into the supplied format of "mmm/yy". The delimiter used to separate the month and year is a forward slash"/".
Use the fill handle to copy the formula to the rest of the column:
Method #2 – Using MONTH & YEAR Functions
The MONTH and YEAR functions can be used individually to get the month and the year from a date. The MONTH function returns the month number from the given date in a single-digit representation. The YEAR function extracts the year from a given date. The functions can be used separately to return individual results and the results can then be combined using a function or a formula to combine the values including a delimiter. Let's see how to use the functions to get the month and year from a date:
The MONTH function
The formula to get the month number from a date using the MONTH function:
=MONTH(B3)The MONTH function takes a single argument as a cell reference of a date. It returns the number of the month from the referenced date.
Apply the formula above to a separate column:
The YEAR function
This is the formula we will use to extract the year from a date with the YEAR function:
=YEAR(B3)Use a separate column to apply this formula. You only need one argument for the YEAR function to get the year from a date. The YEAR function takes the date in cell B3 and returns only the year in four digits:
Using the CONCAT function to Join the Month & Year
The CONCAT function joins a list or range of text strings. Using the CONCAT function, we will join the results of the MONTH and the YEAR functions with our preferred delimiter. For this kind of format 6/2025, our delimiter is a forward slash "/". Now let's see the formula to join the month and year as a single value using the CONCAT function:
=CONCAT(C3,"/",D3)Enter this formula in a separate column. The month number is in cell C3 and the year in D3. The CONCAT function joins the two values using the supplied delimiter i.e. a forward slash "/" in this case:
The CONCAT function can also be replaced by the TEXTJOIN or CONCATENATE function or the ampersand "&" for joining the month and year values.
Method #3 – Using Date Formats
Now, this method probably requires the least effort on your part as we'll be taking our pick from preset options in the Format Cells dialog. For the month and year format, there are only 3 options to choose from so if you don't find what you want, you can head to the Custom section and edit the closest option. For now, here's how to convert date to month and year using date formats:
Since this method is based on formatting the date, it will overwrite the original date format. For the purpose of this tutorial, we have pasted the original date format to another column and will edit that for comparison:
- Select the dates you want in the month and year format.
- Click on the dialog launcher of the Number section in the Home tab or press the Ctrl + 1 keys to go to the Format Cells dialog box.
- The Format Cells dialog box will show you the current format of the date:
- Search the Type: field for a month and year format. There are 3 such formats. Select the format of preference and then click on the OK command button.
The dates will be formatted to the chosen month and year format:
Understanding the Formatting Codes
These formatting codes can be used to return the desired format of the month and year in a date. The codes will be required by some formulas and settings to return a specific format. Below are the codes and the formats that they return:
Months
- m – month in single-digit (e.g. June will be represented by 6).
- mm – month in double-digit (e.g. June will be represented by 06).
- mmm – month name in first three letters (e.g. June will be represented by Jun).
- mmmm – month name in full (e.g. June will be represented by June).
Years
- yy – year in double-digit (e.g. 2025 will be 25).
- yyyy – year in full four digits (e.g. 2025 will be 2025).
Delimiters
Delimiters are separators. You can choose what kind of delimiter you want as part of your custom date format. Here the examples of delimiters in dates:
- "/" Forward slash – e.g. 30/6/25
- "-" Hyphen – 30-6-25
- "." Period – 30.6.25
- " " Space character – June 2025
- "," Comma – June 30, 2025
These codes will help apply custom date formats and the TEXT function.
Recommended Reading: How to Extract Month from Date
That was all on extracting month and year from date in Excel. Be sure to head back any time to this tutorial for your month and year Excel extractions. We'll be back with more extractions and applications for your Excel complications. See you with solutions!
About Mehwish Javaid