3 simple ways to convert the date to month and year in Excel


Excel offers a vast array of functions to enhance content management within the program. Among these functions is the capability to create and customize datasets to suit individual needs. Nevertheless, the abundance of features in Excel sometimes makes it challenging to locate specific functionalities. One such example is the ability to convert dates into month and year formats. Numerous users seek the simplest and most efficient ways to execute this Excel conversion. In this guide, I will demonstrate several methods within Excel that allow you to convert dates into month and year formats effortlessly.

3 simple ways to convert the date to month and year in Excel:

  • Method 1: Use a simple text Formula
    • Input this text formula =TEXT(A1,"m-yyyy") into a cell next to the date to attain the month and the year.
    • Remember to swap out A1 with the cell your date currently sits in.
  • Method 2: Use the Month and Year Formulas
    • Input this formula =MONTH(A1) into a cell next to the date.
    • Input this formula =YEAR(A1) into a cell next to the month.
    • Input this formula =B1 & "-" & C1 into a cell next to the year.
    • Remember to swap the cell IDs (A1,B1,C1) with the cell IDs in your sheet.
  • Method 3: Change the Format
    • In “Home,” click on the dropdown next to “Number Format.”
    • Select “More Number Formats.”
    • Now click on “Custom.”
    • Click the type box and type out “mm-yyyy.”
    • Press “Ok” to complete the process.

You can use these methods to help convert the date to month and year in Excel. The first method will not interfere with the information in the date column. Using formulas, it will simply use that information to create the month and year column. The second method will edit the date column and format to only show the (mm-yyyy).

How to use a simple text formula to separate the month and year from the date

A quick and simple method to separate the month and year from the date is to use the TEXT formula. The text formula allows you to locate information from a cell and extract a copy into a new cell. Follow these steps to see how:

  • First, open Excel and create a new worksheet, or open an existing one.
  • Select the cell next to the date.
  • Input this formula TEXT(A1,”m-yyyy”).
    • Replace the text A1 with the cell corresponding to the date. In my example, the date is in cell A2, so the formula for this example is TEXT(A2,”m-yyyy”).
  • Press enter to see the month and the year.
  • Drag the corner of the cell down to get the month and year from all the date cells.

How to convert the date to month and year in Excel using formulas

The first method you can use to help convert date to month and year in Excel is to use formulas. You can use the individual month and year formula to split the date information into separate columns and then merge that information to get just the month and year. It may seem difficult; however, once you do the process for one row of data, you can simply drag the formulated cell down, and all the required information will be available.

  • Firstly, open your Excel document that you need to retrieve the month and year information.
  • Now select the cell next to the date cell.
  • Add the following “=MONTH(DATE’S CELL ID).”
  • Press enter to show the month.
  • Drag the cell using the fill marker on the bottom right side of the display.
  • To make a column for the year, simply move to the next column.
  • Add the following “=YEAR(DATE’S CELL ID).”
  • Press enter to show the year.
  • Use the fill marker on the bottom right of the cell to fill in all the data for the year.
  • Now move over to the next column.
  • Add the following “(MONTH’S CELL ID) & “-” & (YEAR’S CELL ID)“.
  • Press enter to complete the process.

When you press enter, you can see the month and year of the date column in its separate column. The method shown here will not interfere with the original date column; it will use that column’s information to construct the month and year column. One good feature about this is if you change the date column; for example, if you change the month, the changes will automatically be reflected in the new column.

What can you do to convert the date to month and year in Excel using data formatting?

The next method will directly interfere with the date column and show you the information for the month and year. The method includes formatting the date column to only show the month and year from it. The method shown here is easier regarding reduced steps required to complete; however, the freedom to edit content or view extensive details of the original date will not be available. However, if you are looking for an easier alternative without the formula to get the month and year column, you can use the method shown here.

  • First, open your Excel document with the date column from which you want the month and year.
  • Select the date column; you can do this by clicking the letter at the top of the column.
  • Select “Home.”
  • Click on the dropdown next to “Number Format.”
  • Select “More Number Formats.”
  • Now click on “Custom.”
  • Click the type box and type out “mm-yyyy.”
  • Press “Ok” to complete the process.

Upon completion, you will have managed to convert the date to month and year in Excel. Unlike the previous method, the steps shown here will actively edit the column and show the month and year. You can’t edit the date that includes the actual day as the algorithm has removed that information.

Conclusion

Thank you for reading our Excel content on converting the date to month and year. I have provided three methods you can use to help convert the date to month and year in Excel. The first method uses a simple text formula to extract only the month and year from a date. The second method allows you to add more detail and retain the original date column without issues. The third method will format the date column directly and change the formatting to show only the month and the year. If there are any issues you come across when reading out content, simply drop a comment below, and we will address your issues.

Need help? This is a free service for Business Tech Planet readers. Pop in your problem below and we'll be in touch soon!

Saajid Gangat

Saajid Gangat has been a researcher and content writer at Business Tech Planet since 2021. Saajid is a tech-savvy writer with expertise in web and graphic design and has extensive knowledge of Microsoft 365, Adobe, Shopify, WordPress, Wix, Squarespace, and more! You can connect with Saajid on Linkedin.

Recent Posts