So, if you apply =TEXT(A2, yy) in our example dataset, it will return “18”. yyyy – four-digit representation of year (e.g.yy – two-digit representation of year (e.g.You can use the following two basic format codes to represent year values: We have enlisted below the basic building blocks for the format codes: Format Codes for Year: There are a number of format codes that you can use. For example, if you have a date “” in cell A2, then =TEXT(A2, “mm/yyyy”) will return “02/2018” In the above example, the TEXT function applies the format_code that you specified on the value and returns a text string with that format. format_code is the format you want to convert the cell into.value is the numeric value or reference to the cell that you want to convert.
The TEXT function in Excel converts any numeric value (like date, time, and currency) into text with a specified format. Convert Date to Month and Year using the TEXT Function This is because this method does not provide a lot of flexibility compared to the other two methods we will show next. You will be left with only the converted values that have the month and year.Īlthough this is quite an easy and intuitive way to convert dates to month and year, it is a less popular method. Now you can go ahead and delete columns A to C. In this way, you can display the month and year in any format that you like.įinally, if you want to just keep the converted values and want to remove the original dates and any intermediate columns that you created you need to first convert the formula results into constant values.įor this, copy the cells of column D and paste them as values in the same column (Right-click and select Paste Options->Values from the Popup menu). Now all your cells in column D2 have the new format:Īlternatively, if you want to display your month and year as 2/2018 instead, you only need to replace the “-“ in-between with a “/”. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it.
This should display the original date in our required format.Alternatively, you can type: =MONTH(A2) & “-” & YEAR(A2). Click on a blank cell where you want the new date format to be displayed (D2).Let us say you want to display both month and year as “2-2018” for the date “”, and want to follow this pattern for all the dates. Now let’s see how you can combine the results of the two to display both month and year in a nice format. You will see column C populated by the year corresponding to all the dates of column A. This should display the year corresponding to the original date.Type: =YEAR, followed by an opening bracket (.Click on a blank cell where you want the year to be displayed (C2).Now let’s see how you can extract the year from the same dataset: You will see column B populated by the month of the year for all the dates of column A. This should display the month of the year corresponding to the original date.Click on the first cell containing the original date (A2).Type: =MONTH, followed by an opening bracket (.Click on a blank cell where you want the month to be displayed (B2).Let’s see how you can extract the month from our sample data: If not, then both these functions will return a #VALUE error. In order for this method to work, the original date (on which you want to operate) must be a valid Excel date. The MONTH and YEAR functions can help you extract just the month or year respectively from a date cell. You don’t want the date 2/10 to be treated as October, the 2nd instead of February, the 10th! Convert Date to Month and Year using the MONTH and YEAR function So when entering the date, make sure you check the format and enter the date in the correct order. In still other places like China, Iran, and Korea, the order is completely flipped (yyyy/mm/dd).ĭepending on your computer’s date settings Excel will treat parts of your date differently. In the UK and other countries, dates begin with the day and end with the year (dd/mm/yyyyy). For example, in the US format, dates usually begin with the month and end with the year (mm/dd/yyyy). When working with dates, first and foremost, it is important to recognize the original format your Excel dates are in. We will be converting these dates to month and year in Excel: Throughout this tutorial, we are going to be using the following set of dates. Convert Date to Month and Year using Number Formatting.Convert Date to Month and Year using the TEXT Function.Convert Date to Month and Year using the MONTH and YEAR function.