How to Convert Date to Month and Year in Excel

Microsoft Excel is a very popular spreadsheet software used by various companies and individuals alike. Most information captured in an Excel spreadsheet is likely to contain various dates. 

You may, however, require a specific format for your date. For instance, you may be just interested in having all your dates captured as just the month and the year. If so, then you would probably have asked yourself the question “How to convert date to month and year in Excel”.

Excel automatically recognizes the format of dates and converts the cell in which the dates were entered into the date cells. 

The moment you type a date into a Microsoft Excel spreadsheet, it automatically identifies which part of it is the day, the month, and the year accordingly. Excel can be said to be pretty smart when it comes to dealing with dates.

This tutorial is designed to guide you step by step accompanied by graphics on how to convert date to month and year in an excel spreadsheet. 

You will therefore explore all the methods there are to doing this. Join me, let’s tap right into it.

Quick Note: 

The date format usage varies from country to country so you need to be aware of the exact format you want to work with. Make sure your computer’s date format is similar to what you want. If you are not sure then you can follow the steps below to set the right date format.

How to change date format in excel

After typing your date into a cell, click the keyboard shortcut (CTRL + 1) to bring up the dialog box to change the format for your date to what you want.

Now, go through the various formats shown under “Type” and choose the format you want before proceeding with the rest of this tutorial.

Method One: Convert date to month and year using the month and year function

There is a function in Microsoft Excel called the month function and the year function. These functions can aid you to extract only the month or year from a cell containing a date. 

The validity of the date in the cell you want to apply this formula to is very important. You must ensure that the cell contains a valid date before applying the formula to that cell. Otherwise, you will end up with an error (#VALUE error).

In order to convert a date to Month and Year in Excel, follow the steps below:

For the purposes of this demonstration, we will have the full date (dd/mm/yyyy) in column A and we will extract the month into column B while we extract the year to column C.

So make sure you have your full date in one column then follow the steps below to convert the full date into months and years.

• Under the column where you want to have the months, click to select a blank cell (eg: Click on cell B2)
• To get the value for the month, we use a function called “MONTH”. Hence type =MONTH()
• Into the brackets in front of MONTH, type in the name of the specific cell which date you want to extract the month from. That will be cell A2 (having the date 02/12/2006) in this example.

• You are therefore supposed to have something like =MONTH(A2) and click on Enter
• The month will then automatically be generated in cell B2 as you can see in the image below.

Here is the outcome of the above step

The next thing to do is to apply this formula to the remaining cells. To do that, pull down the fill handle over the remaining cells within the column where you want the months to be populated. 

The steps to extract the year from the full date is similar to the steps above except that the function we will use in this case is =YEAR().

To get only the year, follow the steps below:

• Click to select a blank cell under column C (eg: cell C2) 
• Type the function =YEAR()
• Inside the bracket in front of the year function, type in the identity of the cell containing the full date (A2 in this example)
• Click the enter key to apply the function

• This should provide the year in which the original date occurred. By pulling down the fill handle over the rest of the cells under that column, the cells will be populated with their respective years. 
• You will see column C populated by the year corresponding to all the dates of column A.

The next thing we want to look at is how to merge the two major steps discussed above. How can we generate both the month and year together from a full date? Now let’s see how both results can be merged to display both month and year.

For Instance, let’s say you want to display both month and year as “12/2006” for the full date “06/07/2009” and use this format for several other dates present in your spreadsheet. 

By still using the sample data used earlier on, let’s mark column D for our month and year together.

• Click and select a blank cell under column D (eg: cell D2)
• Go ahead and type the formula =B2&”/“&C2

Note: This is using the & (representing AND) to join the values in columns C and B with a “/” separating them. This means you could practically choose any separator of your choice.

Another way to achieve the same result without going through the first two major steps (separate month, and separate year before combining them) is to apply the joining with & on the functions (=Month & =Year).

To do this, click the cell you want to display the month and year in and type the formula below into it.

=MONTH(A2)&”/”&YEAR(A2)

• This should display the original date in our required format. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it.

Now all the cells in column D2 have the new format:

You can also replace the “/” in between the month and year with a “-” if you want to display your month and year as 12-2006 instead. 

Finally, 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.

For 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 you can go ahead and delete columns A to C. You will be left with only the converted values that have the month and year.

Although this is a simple and intuitive technique of converting dates to months and years, it is not often used. This is because, in comparison to the other two approaches we’ll discuss next, this method offers less flexibility.

Method 2: Convert Date to Month and Year using the TEXT Function

In Excel, the TEXT function turns any numeric data (such as a date, time, or currency) into text in a defined format.

The TEXT function has the following syntax:

(value, format_code) = TEXT

In this context;

• Value is the numeric value or reference to the cell that you want to convert
• Format_code is the format you want to convert the cell into.

The TEXT function in the preceding example applies the format code you gave to the value and returns a text string with that format. For example, if cell A2 has the date “2/10/2018,” =TEXT(A2, “mm/yyyy”) will produce “02/2018.”

You can use a variety of different format codes. The essential building components for format codes are described below:

Format Codes for Year:

The following two basic format codes can be used to represent years values:

• yy – two-digit representation of year (e.g. 20 or 12)
• yyyy – four-digit representation of year (e.g. 2020 or 2012)

So, if you apply =TEXT(A2, yy) in our example dataset, it will return “06”.

If you apply =TEXT(A2, yyyy), then it will return “2006”.

Format Codes for Month of the Year:

You can use the following four basic format codes to represent month values:

• m – one or two-digit representation of the month (eg; 5 or 10)
• mm – two-digit representation of the month (eg; 05 or 10)
• mmm – month abbreviated in three letters (eg: May or Oct)
• mmmm – month expressed with the full name (eg: May or October)

So, if you apply =TEXT(A2, m) in our example dataset, it will return “2”.

• If you apply =TEXT(A2, mm), then it will return “02”.
• If you apply =TEXT(A2, mmm), then it will return “Feb”.
• If you apply =TEXT(A2, mmmm), then it will return “February”.

Let’s look at how we can use the TEXT function to convert all of the dates in our sample collection to different formats.

We’ll start by converting the dates in column A to the format shown in column B in the following image:

The steps to modify the date format to simply get the month and year using the TEXT function are as follows:

• Select a blank cell in which you wish the new date format to appear (B2)
• Fill In the formula “=TEXT(A2,”m/yy”)”
• Press the enter key

This should show the original date in the format we desire. By pulling down the fill handle or double-clicking on it, you may copy this to the remainder of the cells in the column.

• By hitting CTRL+C (if you’re on a Mac) or Cmd+C (if you’re on a PC), you can copy the formula results from this column.
• Right-click on the column and select Paste Values from the Paste Options menu that shows.
• The formula results will be saved as permanent values in the same column. If you like, you may now delete column A.

The format code you use in the formula (step 2) will change depending on how you want your month and year to appear. The format codes are listed below, along with the type of result you’ll get when you apply them to cell A2:

In a summary, the picture below illustrates how each text function and format code display the dates in excel 

As you can see, the TEXT method can be used to convert dates to any format you like. All you have to do is update the format code to suit your needs.

Method 3: Convert Date to Month and Year using Number Formatting

The Format Cell function in Excel is a flexible tool that allows you to do a variety of formatting tasks from a single dialog box.

The following steps will show you how to convert the dates in our sample dataset to various formats.

• Select all the cells containing the dates that you want to convert (A2:A6).
• Right-click on your selection and select Format Cells from the popup menu that appears. Alternatively, you can select the dialog box launcher in the Number group under the Home tab.

• This will open the Format Cells dialog box. Click on the Number tab
• Under Category on the left side of the box, select the Date option.
• This will display a number of formatting options for date on the right side.
• Select the format that you want. For example, if you want to display the first date in the format “Feb-18”, then select the matching format option.

• You can use the Custom option from the Category list on the left if you don’t see a choice for the format you wish to use. This allows you to change the format of the cell.
• Check the date format codes under Type to see if your format is supported. If you don’t have a format code, type it in the input box directly below Type. So, if you want the first month to be displayed in the format “2/06,” use “m/yy.”

• Click OK to close the Format Cells dialog box
• All your selected cells should now be formatted to your required format.

In three respects, this method varies from the previous one (which used the TEXT function):

You can conduct the operation directly on the original cells using this way.

You can complete your conversion in one sitting. So you don’t need to enter the formula in a new cell, paste by value, and then delete the original cells (as you would need to if using the TEXT function).

This approach only modifies the format of the original date; the underlying date stays unchanged. You can simply obtain the original date value (along with the day) if you need it later. The original date value is lost when using the TEXT function method since the conversion alters the complete date value.

Because the results of this function are of the type Date rather than Text, you may directly execute date operations on them without having to convert them.

These are the three methods for converting a date to a month and year in Excel. You can convert your date to any format you like with these.

We hope you found our techniques beneficial and will use them to analyze your own Excel data.