Learn How to Convert Dates to Month and Year Format in Excel

Mastering date manipulation is a fundamental skill in Excel. Often, raw date data needs to be presented in a specific format, such as just the month and year, for reporting or analysis. This guide provides four reliable formulas utilizing the versatile TEXT function to convert a standard date into various month-and-year representations.

The Four Primary Conversion Formulas

The TEXT function is essential here because it allows us to apply a Custom number format to a date value, converting the underlying serial number into a readable text string. Below are the core formulas required, assuming the source date resides in cell A1.

Formula 1: Convert Date to Month and Full Year (e.g., 01/2022)

=TEXT(A1, "mm/yyyy")

Formula 2: Convert Date to Month and Last Two Digits of Year (e.g., 01/22)

=TEXT(A1, "mm/yy")

Formula 3: Convert Date to Abbreviated Month and Full Year (e.g., Jan. 2022)

=TEXT(A1, "mmm. yyyy")

Formula 4: Convert Date to Full Month and Full Year (e.g., January 2022)

=TEXT(A1, "mmmm yyyy")

It is important to understand the role of the format codes (e.g., “mm,” “mmm,” “yyyy”). The number of ‘m’ or ‘y’ characters dictates the level of detail and abbreviation used in the resulting Date format. The following sections demonstrate these formulas in practical, step-by-step examples, illustrating how to apply them to an entire column of data.

Example 1: Converting to Month Number and Full Year (mm/yyyy)

This first example is ideal when you require a simple, numerical representation of the month followed by the complete four-digit year. This format is commonly used for standardized record-keeping and data sorting, ensuring clarity regarding the century. We will apply the formula to the date values located in column A, starting with cell A2.

To initiate the conversion process, enter the following formula into cell B2. This formula instructs Excel to read the date in A2 and output it as a two-digit month number and a four-digit year.

=TEXT(A2, "mm/yyyy") 

After entering the formula into cell B2, use the fill handle feature to drag the formula down, applying it to all subsequent cells in column B. This action instantly transforms the original dates in column A into the desired “month/full year” structure, as shown in the accompanying illustration.

Observe that column B now accurately displays the month number and the complete four-digit year (YYYY) for every corresponding date found in column A.

Example 2: Converting to Month Number and Two-Digit Year (mm/yy)

In scenarios where space conservation is necessary or when dealing with recent historical data, using a two-digit year representation can streamline the output. This format is achieved by using “yy” instead of “yyyy” in the format argument of the TEXT function.

To implement this conversion, input the following concise formula into the first target cell, B2, referencing the date in A2. The “yy” argument ensures that only the last two digits of the year are extracted and displayed.

=TEXT(A2, "mm/yy") 

Once the formula is entered, drag the calculation down through column B to quickly populate the results for all dates. This powerful feature of Excel automates the transformation across the entire dataset.

The output in column B demonstrates the clean “month/two-digit year” Date format (MM/YY) for each corresponding entry in column A.

Example 3: Converting to Abbreviated Month Name and Full Year (mmm. yyyy)

For reports that prioritize readability without sacrificing vertical space, converting the date to an abbreviated month name is highly beneficial. Using three ‘m’ characters (“mmm”) in the format argument instructs the TEXT function to return the first three letters of the month name, followed by the full four-digit year.

Implement the following formula in cell B2, ensuring that the reference A2 points to the first date entry you wish to convert. Note the period used after “mmm” for standard abbreviation punctuation, which is included within the quoted format string.

=TEXT(A2, "mmm. yyyy") 

As with the previous examples, drag the formula down from B2 to apply this custom abbreviation format across the entire column. This method quickly provides a clear, human-readable date string that clearly identifies the month and year.

The resulting column B shows the abbreviated month name (e.g., Jan.) and the corresponding full year for each date entry.

Example 4: Converting to Full Month Name and Full Year (mmmm yyyy)

The most verbose and descriptive method involves displaying the complete month name alongside the full year. This is achieved by using four ‘m’ characters (“mmmm”) in the format argument. This format is highly desirable for formal documents, presentations, or any output where clarity is paramount and space is not a limiting factor.

To achieve this full-text conversion, enter the formula below into cell B2. This specific format string will trigger Excel to spell out the entire month name.

=TEXT(A2, "mmmm yyyy") 

Once entered, the formula can be rapidly extended down column B using the drag-and-fill functionality. This ensures that every date in column A is instantly transformed into its long-form month and year equivalent.

Column B successfully displays the full month name and the complete four-digit year for every date, providing the clearest possible Date format output.

Summary of Date Formatting Codes

The key to successful date conversion in Excel lies in understanding the specific codes used within the TEXT function‘s format argument. These codes dictate how the underlying serial date value is presented to the user.

1. “mm”: Displays the month as a two-digit number (e.g., 01 for January).
2. “mmm”: Displays the month as a three-letter abbreviation (e.g., Jan).
3. “mmmm”: Displays the full month name (e.g., January).
4. “yy”: Displays the year as a two-digit number (e.g., 22 for 2022).
5. “yyyy”: Displays the year as a four-digit number (e.g., 2022).

By combining these codes with separators like “/” or spaces, you can achieve virtually any desired date structure.

Additional Resources for Data Transformation

If you are looking to further enhance your data manipulation skills within Excel, the following advanced tutorials explain how to perform other common and complex tasks related to data transformation and analysis.