Convert a Date into a Month and Year in Excel: Quick Guide for Easy Formatting

Excel is quite the handy tool for managing and transforming data. Often, we come across situations where we need to extract the month and year from a date value for reporting or organizing. Fortunately, Excel offers a couple of straightforward functions that make this task a breeze. Whether you’re prepping a monthly sales report or organizing a timeline, knowing how to isolate the month and year from a full date can simplify your workflow significantly.

In my experience with Excel, I’ve found that some methods are more intuitive than others, allowing you to quickly reshape your data without breaking a sweat. These functions are not only simple to use but also incredibly powerful when it comes to sorting, filtering, and visualizing data trends over time. It’s time to dive into the simple steps that will let you convert dates into month and year components.

Use Custom Formatting to Convert Date into a Month, Year, or Month and Year

When I need to display dates as just the month and year in Excel, custom formatting is my go-to solution. Here’s how I do it:

I select the cell that has the date I want to reformat.

Pressing Ctrl + 1 brings up the Format Cells dialog box.

In the Format Cells box, I click on the Custom category to get more control over the display.

Then in the Type field, I input the desired format, like “mmm-yyyy” for abbreviated month name and year.

After clicking OK, my cell updates to show just the month and year.

Below are the formatting options I might use, depending on my needs:

mmm: Display “Jan” for January
mm: Show the month as a number, like “01”
mmmm: Get the full month name, “January”
yy: Get the year in two digits, like “24”
yyyy: See the year in four digits, “2024”

Even though on the surface the cell shows only the month and year, it’s essential to remember that Excel still retains the complete date value internally. This way, my data remains intact, and I can always revert to the full date if needed.

Convert into Month and Year into a Separate Cell

When I work with dates in Excel and I need just the month and year in a separate cell, the TEXT function is my go-to solution. Here’s how I do it:

I start by entering =TEXT(A1,"mmm-yyyy") into the cell where I want to see the formatted date. The TEXT function needs two pieces of information:

The value to convert, which is the date in cell A1.
The format_text where I specify how I’d like to see the result.

For the format, I can choose from options like:

mmm for the abbreviated month
yyyy for a four-digit year
mm-yyyy for the month and year together

This works great, and if I have a long list, I simply drag the fill handle down to apply this formula to the rest of the cells. Here’s a quick reference for some common formats I use: