If you have a Microsoft Excel document with date or with date and time, but you want the document to include only month and year, you can use a Function or create a formula in Excel to make this happen very easily.


This blog post provides the information you need to use function and formula to extract month and year only from date in Excel.


How to convert date to month and year in Excel

Open Microsoft Excel, and then open the document that you want to include only the month and year.

With the Excel home tab selected, click the Insert Function icon. If you don’t see the Insert Function icon, select the Formulas tab, and then click Insert Function.

In the Category, select Text, then double-click Text in the Select a Function section or select Text, and then click OK.

To use column A in the first row, type A1 in Value or click A1 in the document.

In Format_text, type mmm-yyy

Click OK.

To auto-fill all cells, drag the filled cell down until you fill all the cells.


Excel formula to change date to month and year

Select an empty cell.

Type the equal sign =

Type Text, open parentheses sign (

Select the cell you want result from or type its address

Type comma sign, quotation mark sing “mmm-yyy quotation mark sign, then close parentheses. Example,”mmm-yyy”)

When you are done, the formula for changing date to month and year will be something like below.


For address A1, below is the formula to enter in the empty cell.

=TEXT(A1,”mmm-yyy”)


For address A4, below is the formula to enter in the empty cell.

=TEXT(A4,”mmm-yyy”)


Press Enter after entering the formula.


Video to show you how to get month and year from date in excel​

What To Read Next


If you like the content of this post or if it has been useful to you, please consider sharing it on your social media and follow me on Facebook and Twitter for more exclusive content.