How to Convert a Date to Month and Year in Excel: 6 Best Methods

ByBipasha Nath

Do you want to change a date to only show the year and month in Excel?

There are many different formats to display the date. In certain use cases, the day does not bear any significance and could only cause confusion to the users.

Hence, converting such dates into month and year format is the right choice. This conversion helps to hide sensitive data like DOB for security reasons.

In this tutorial you will learn the best methods to convert a date to month and year in Excel. 

Convert Date to Month and Year with a Custom Format 

The Custom Format is by far the simplest way to get month and year from a date.

Here’s how it works using the custom format options.

  1. Select the cell range where you’ve got date values on your Excel worksheet.
  2. Press the Ctrl + 1 keys together to bring up the Format Cells dialog box. 
  3. Under the Category column, choose Custom and enter the mm-yy code into the Type field. 
  4. Click OK to transform the existing long dates to MM-YY format values. 

Above you can see the results from the custom format.

Find below some other codes and their outputs that you can use in your Custom Format.

  • mmmm-yyyy for June-1987 format.
  • mm-yyyy for 06-1987 format.
  • mm/yy for 06/87 format.

Convert Date to Month and Year with a Formula

If you love to use formulas in Excel and are looking for some quick formulas to convert dates into months and years, I’ve got the following formulas for you.

Find the detailed formula along with the steps to use those below.

Convert Date to Month and Year with the TEXT Function 

Using Text formula 

  1. Create a new column header beside the column that contains dates. 
  2. Select the first cell below the new column adjacent to the first date in your data. 
  3. Enter the following formula into the selected cell and hit Enter.
=TEXT(B2, "mm-yyyy") 
  1. Use the fill handle to copy and paste the above formula down the entire column until the point date value exists in the adjacent column. 
  2. Excel will instantly convert dates to months and years.

You can use the following alternative formatting in the above formula for respective outputs.

  • mmm-yyyy for Jun-1987 
  • mmmm-yyyy for June-1987 
  • mm-yy for 06-87

In the above formula, you must change B2 to an appropriate cell address where the date value exists. 

Convert Date to Month and Year with the MONTH and YEAR Functions 

Using month and year functions 

  1. Assuming your date is in cell B2, enter the following formula in an empty cell adjacent to the date, like cell C2: 
=(MONTH(B2)&"-"&(YEAR(B2))) 
  1. Hit Enter to convert the date into a month and year value. 
  2. Use the fill handle to apply the same formula across all the cells of the above column until the point a date value exists. 

This formula uses the MONTH function to extract the month from the date and the YEAR function to extract the year. Then, I used ampersand to concatenate the months and years in a single cell. Feel free to change the cell address value B2 to something else according to your date table on the Excel sheet.    

Convert Date to Month and Year with the CONCATENATE Function 

concatenate function 

  1. Suppose, you got the date values under column B and the first date is in cell B2. 
  2. Select the adjacent cell C2 and type in the following formula into the cell: 
=CONCATENATE(TEXT(B2,"mmm"),"-",YEAR(B2)) 
  1. Hit Enter to get Jun-1987 in the selected cell C2. 
  2. Copy and paste the same formula in the rest of the cells of column C to transform dates into months and years. 

In the above formula, I used the TEXT function to fetch the month and the YEAR function to get the year from the given date. Then, I wrapped these functions inside the CONCATENATE function to join both results.  

You could modify the formula into the following to get months and years in the MM-YYYY format, like 06-1987: 

=CONCATENATE(TEXT(B2,"mm"),"-",YEAR(B2))   

Convert Date to Month and Year with a Power Pivot Calculated Column 

Do you need to perform custom calculations, normalize & standardize data, and clean up inconsistencies when converting dates to months and years? You can use the following Power Pivot Calculated Column method: 

Enabling Power Pivot Add-In in Excel 

Power Pivot isn’t readily available on your Excel desktop app. You must activate it manually when you use the add-in for the first time. Also, the add-in isn’t available for Excel desktop apps prior to Excel 2010. Here’s how to activate this Excel add-in: 

Excel options 

  1. Click File on the Excel ribbon and choose Options from the left-side navigation pane. 
  2. Select Add-ins on the left side menu of Excel Options. 
  3. Click the Manage drop-down menu on the right and choose COM Add-ins and hit the Go button. 

Power pivot 

  1. On the COM Add-ins dialog box, checkmark the checkbox for Microsoft Power Pivot for Excel and hit the OK button. 
  2. Power Pivot shall show up as a new Excel ribbon tab. 

Using Power Pivot Calculated Column    

Power pivot open table 

  1. Select the table where you got a column for the date values (column B). 
  2. Click the Power Pivot tab on the Excel ribbon and select the Add to Data Model command inside the Tables block. Click OK on the pop-up. 
  3. Your data open as a table inside the Power Pivot add-in. 

Converting to mm yy in power pivot 

  1. There, click the Design tab above the ribbon menu and click the Add button inside the Columns command block. 
  2. A new column shows up and the cursor appears inside the formula bar of the Power Pivot add-in. 
  3. There, copy and paste the following formula: 
=FORMAT([Date of Birth], "mmm-yyyy") 
  1. Hit Enter and a new Calculated Column 1 shows up to the right side of the original date values. 
  2. The Power Pivot tool applies the formula for all the date values in column B automatically. 
  3. Click File on the Power Pivot ribbon and choose Save from the context menu and close Power Pivot. 

Converted months years on power pivot 

  1. On the Excel worksheet, select the Data tab and hit the Refresh All button inside the Queries & Connections block. 
  2. The new Calculated Column 1 will show up on the right side of column B. 
  3. The new column should show the extracted months and years in Jun-87 (MMM-YY) format. 

In the above formula, I used [Date of Birth] to indicate column B, where I had all my short date values. You could use the column header in place of [Date of Birth] according to your own worksheet.     

Convert Date to Month and Year with Power Query 

If you’re importing dates from an external server and need to convert those dates to months and years on Power Query and load to Excel, follow these steps: 

  1. On Excel sheet, click Data tab and select Get Data. 
  2. Choose your data source and Power Query tool will open. 

custom columns 

  1. There, click Add Column and then select Custom Column. 
  2. On the Custom Column dialog box, give the new column a name, like MM. 
  3. Enter the following formula in the formula field: 
Date.Month([Date of Birth]) 
  1. Click OK to extract month from date and put that under column MM. 
  2. Repeat the above step but name the new column as YYYY and use this formula: 
Date.Year([Date of Birth]) 
  1. Hit OK to extract year from date and put that under column YYYY. 

merging columns 

  1. Press Ctrl to select both the MM and YYYY columns. 
  2. Click Merge Columns, choose any separator, like hyphen and give the new column a name, like MM-YYYY. 
  3. Hit the OK to get your months and dates. 
  4. Click File and choose Close & Load to import data into the Excel worksheet. 

transformed data from power query 

[Date of Birth] is the column header that contains dates. Customize the column header as per your data.

Convert Date to Month and Year with VBA 

If there are thousands of dates in your worksheet that need converting to months and years, you can use the following VBA script. The code offers a one-click solution for the conversion. Here are the steps you must try: 

creating VBA scripts 

  1. Hit Alt + F11 to launch the VBA Editor. 
  2. Click Insert on the toolbar and choose Module. 
  3. Inside the new module, copy and paste the following VBA script: 
Sub ConvertDateToMonthAndYear()
    Dim rng As Range
    Dim cell As Range
    Dim destRange As Range
    
    ' Set the range where the date values are located
    Set rng = Range("B2:B7") ' Update the range as per your data
    
    ' Set the destination range in column C from C2 to C7
    Set destRange = Range("C2:C7")
    
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell contains a valid date
        If IsDate(cell.Value) Then
            ' Convert the date to month and year format
            destRange.Value = Format(cell.Value, "mmm-yyyy")
        End If
        
        ' Move to the next cell in the destination range
        Set destRange = destRange.Offset(1)
    Next cell
End Sub
  1. Click the Save button. 
  2. A dialog box will pop up to prompt you to save the Excel workbook as a macro-enabled workbook. Click Yes to proceed. 
  3. Close the VBA Editor. 

running vba 

  1. Now, hit the Alt + F8 keys together to bring up the Macro dialog box. 
  2. There, select the ConvertDateToMonthAndYear macro and click the RUN button. 
  3. Excel will convert the dates in column B to months and years in column C. 
  4. The format of month and year will be MMM-YY, like Jun-87. 

Before running the above VBA script, change the following cell ranges according to your own Excel worksheet: 

  • Range(“B2:B7”): source cell range for short dates 
  • Range(“C2:C7”): destination cell range for months and years   

Convert Date to Month and Year with Office Scripts

If you need to automate the process on Excel on the Web, you can use the following Office Scripts code to convert a short date to month and year: 

Office scripts 

  1. On Excel online or Excel 365 desktop app (requires Office 365 A5 or better subscription) click the Automate tab.  
  2. Click New Script inside the Scripting Tools block to open the Code Editor on the right-side panel. 
  3. Now, copy and paste the following code inside the Code Editor: 
function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C1:C2 on selectedSheet
	selectedSheet.getRange("C1:C2").setFormulasLocal([["MM-YY"],["=TEXT(B2,\"mm-yy\")"]]);
	// Set font bold to true for range C1 on selectedSheet
	selectedSheet.getRange("C1").getFormat().getFont().setBold(true);
	// Paste to range C3:C7 on selectedSheet from range C2 on selectedSheet
	selectedSheet.getRange("C3:C7").copyFrom(selectedSheet.getRange("C2"), ExcelScript.RangeCopyType.all, false, false);
}
  1. Click the Save script button and hit the Run button to execute the script. 
  2. Excel will create MM-YY column header in column C and populate the months and years in MM-YY format from C2 through C7. 

Customize the above script in the following code elements: 

  • Set range C1:C2: change it to the first two cells of the column where you want the converted values 
  • B2: change it to the first cell of the source of the short dates 
  • range C3:C7: change this to an appropriate cell range according to the destination you choose 

Conclusions

Now you know the best approaches to convert date to year and month in Excel. If you’re a beginner, use methods that use Custom Formats and formulas.

For mid-level Excel users dealing with databases, Power Pivot and Power Query are the ideal options. However, if you’re an expert with experience in scripts, try using VBA scripts and Office scripts. 

Similar Posts