We can use the TEXT function to make Excel convert dates to month and years.

Figure 1. Text Function in Excel

Generic Formula


If we want to convert an Excel date to month and year  format (e.g. 05/19/2019 > 052017), we can use TEXT function.

How to Change Dates to Month and Year in Excel

We will have to generate an Excel formula to convert dates to month and year

Our formula will be based on the TEXT Function.

The TEXT function in Excel applies our specified number format to a number value, and generates a text result.

Here’s how to make Excel convert to month and year only;

  1. We begin by gathering the number values we want to convert into our spreadsheet;

Figure 2. Dates in Excel

Be sure to provide an empty column of cells for Excel to return the converted results (Column B above) 

  1. Our formula for Excel to convert date to month and year is as follows;


Figure 3. Convert Dates in Excel

  1. Modify and copy the Excel formula to convert dates to month and year (in cell B2 above) down into the other columns in column B to get similar results;

Figure 4. Convert Dates in Excel

How to Convert a Date to a Month in Excel

If our goal is to convert dates to months only, we will simply adjust the components of our TEXT formula to convert dates to month and year (used above).

Our Excel formula to convert date to month in cell B5 below is as follows;


Figure 5. Convert Dates to Month in Excel

How to Convert Date to Year in Excel

If we want to make Excel convert dates to year only, we modify our TEXT formula as follows;


Figure 6. Convert Dates to Year in Excel