How to Extract Month and Year from Date in Excel: Easy Formula Guide

Sharing is caring!

Do you have a spreadsheet in Microsoft Excel filled with dates and need to extract just the month and year from those dates? You’ve come to the right place. In this article, we’ll show you the exact Excel formula to use to quickly convert a full date into just the month and year format. Whether you’re a beginner or advanced Excel user, this step-by-step guide will walk you through the process.

Why Extract Month and Year from Date in Excel?

There are many reasons you may need to get the month and year from a date in Excel:

• Analyzing data by month to spot monthly trends or patterns
• Preparing monthly financial reports or statements
• Organizing data into a monthly calendar or schedule
• Filtering a list of dates to focus on a specific month and year
• Breaking out date information to use in a pivot table or chart
• Grouping or sorting data by month for better visualization and insights
• Calculating month-over-month or year-over-year changes in values
• Comparing metrics or performance across different months or years
• Creating a monthly budget or forecast model
• Generating a monthly invoice or billing statement

Rather than manually entering the month and year for each date, which would be incredibly time-consuming, you can use a simple Excel formula to automatically pull this information from the full dates. This will save you a ton of time and ensure accuracy.

Excel Formula to Extract Month and Year from Date

Here is the Excel formula to use to convert a date into the month and year format:

=TEXT(A1,”mmm yyyy”)

To use this formula:

1. In a blank cell, type =TEXT(
2. Click on the cell containing the first date in your list, or type in the cell reference
3. Type ,”mmm yyyy”) to close the formula
4. Hit Enter to get the result
5. Drag the formula down to apply it to the rest of your date list

The “mmm” part of the formula represents the 3-character month abbreviation (e.g. Jan, Feb, Mar). The “yyyy” part represents the 4-digit year. You can adjust this based on your preferred format:

Feel free to choose whichever month and year style you prefer. The overall formula structure remains the same.

Formula Breakdown

Let’s look at each part of the TEXT formula to understand how it works:

• =TEXT( – This function converts a value to text in a specific number format
• A1 – This is the cell reference containing the date you want to convert. Replace A1 with the actual cell containing your date.
• ,”mmm yyyy”) – This specifies the format you want to convert the date into, with “mmm” representing the 3-letter month abbreviation and “yyyy” representing the 4-digit year. The date format goes inside quotation marks.

Essentially, the formula is taking the full date in cell A1 and converting it to a text string showing just the month and year.

Handle Blank or Text Dates

If some of your cells have text dates or are blank, you may get a #VALUE! error when you try to use the TEXT formula to extract the month and year. To fix this, we can use the DATEVALUE function to first convert the text date to a real date that Excel understands.

Blank cells will show up as 1/0/1900 when you use the DATEVALUE function on them. To handle this, we can add an IF function to the formula to leave blank cells blank. Here’s the updated formula:

=IF(A1=””,””,TEXT(DATEVALUE(A1),”mmm yyyy”))

Now if cell A1 is blank, the formula will return a blank value. If A1 has a date, the formula will convert it to a month and year.

Here’s the step-by-step breakdown:

1. Start with =IF(A1=””, to check if cell A1 is blank
2. ,””, specifies to leave the result blank if A1 is blank
3. TEXT( starts the TEXT function if A1 is not blank
4. DATEVALUE(A1) first converts the text date in A1 to a real date
5. ,”mmm yyyy”) tells Excel to convert the date to 3-letter month and 4-digit year
6. Close off the TEXT function with )

With this expanded formula, you can handle any mixture of real dates, text dates, and blank cells.

Extract Month and Year with Partial Text Dates

What if you have partial text dates like “Jan 2023” or “March 15” where the year or day is missing? You can still use a formula to extract the month and year:

For dates like “Jan 2023” use:
=TEXT(DATEVALUE(“1 “&A1),”mmm yyyy”)

This assumes the day is the 1st of the month if not specified. The “&A1” piece adds your partial date from cell A1 to “1 ” to create a full date Excel can understand.

For dates like “March 15” that are missing the year use:
=TEXT(DATEVALUE(A1&” 2023″),”mmm yyyy”)

This takes your partial date in cell A1 and adds ” 2023″ to the end to give it a year. Update 2023 to the actual year you want to use. Then the TEXT formula can extract the month and year as usual.

Troubleshooting Tips

If you’re still getting errors or unexpected results when trying to extract month and year from your dates, here are a few troubleshooting tips:

• Make sure your original dates are actually stored as dates in Excel and not as text that looks like dates. Select a date cell, go to the Home tab, and look in the Number group. The cell should have a “Date” format.
• Check that you don’t have any extra spaces in your date cells before or after the date. Even small spaces can cause issues.
• Remove any other text that may be combined with your dates, like labels or notes. You only want the date itself in the cell.
• Verify you’re using the correct cell references in your formulas. Make sure you’re pointing to the cells you intend to reference.
• If your dates are in a format Excel doesn’t recognize by default, you may need to use the DATEVALUE function first to convert them to real dates. Try =DATEVALUE(A1) and see if it returns a valid date.

By double-checking these common formatting issues, you can resolve most problems that come up when extracting month and year from dates in Excel.

Summary

As you can see, extracting the month and year from a date in Microsoft Excel is incredibly easy with the TEXT formula. Simply use =TEXT(A1,”mmm yyyy”) to convert a date in cell A1 to a 3-letter month abbreviation and 4-digit year.

You can swap in “mmmm” for the full month name or “mm” or “m” for other month formats. You can also change the year to “yy” for a 2-digit year. Update the cell reference from A1 to the actual cell containing your first date. Drag the formula down to extract the month and year from the rest of your date list.

To handle text dates or blank cells, expand the formula to:

=IF(A1=””,””,TEXT(DATEVALUE(A1),”mmm yyyy”))

This will leave blanks cells blank while still converting any text dates to real dates the TEXT formula can handle.

For partial text dates missing the day or year, use:

=TEXT(DATEVALUE(“1 “&A1),”mmm yyyy”) for dates like “Jan 2023”

=TEXT(DATEVALUE(A1&” 2023″),”mmm yyyy”) for dates like “March 15”

Whether you need month and year for data analysis, reporting, scheduling, filtering, grouping, calculations, comparisons, budgeting, invoicing, or any other spreadsheet tasks, this Excel TEXT formula has you covered. It’s a quick and simple way to get the date format you need.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.