AI Excel Generator
← ALL GUIDES

How to Extract the Month and Year from a Date in Excel

Updated November 4, 2026 · AI Excel Generator

To extract a month and year from a date in Excel, you can display them together as text, create a real date for the first day of that month, or return the month and year separately.

The right approach depends on whether you need a readable label or values you can sort, calculate, and chart reliably.

Format a date to show the month and year

If the original date is in cell A2 and you only need it to appear as a month and year, apply a custom number format.

Select the date cells, open Format Cells, choose Custom, and enter mmmm yyyy.

A date such as June 18, 2026 will display as June 2026, while the cell still contains the original date.

For a shorter label, use mmm yyyy to display Jun 2026, or mm/yyyy to display 06/2026.

In a custom format, mmmm means the full month name, mmm means its abbreviation, and yyyy means the four-digit year.

Use mm for a two-digit month number.

This is often the best choice when you want a column to look grouped by month but still need Excel to treat each cell as a date.

Sorting and date calculations continue to use the underlying dates, not the displayed text.

The format changes how the date appears; it does not remove the day from the stored value.

Use TEXT to return a month-year label

To put the month and year into a separate cell as text, use Excel's TEXT function.

With a date in A2, enter:

=TEXT(A2,"mmmm yyyy")

If A2 contains June 18, 2026, the result is June 2026.

Use =TEXT(A2,"mmm yyyy") for Jun 2026, or =TEXT(A2,"mm/yyyy") for 06/2026.

Copy the formula down to create a label for each date in a list.

TEXT returns text, not a date value.

That makes it useful for report headings, labels, and concatenated descriptions, but the result may not sort chronologically as expected.

For example, alphabetical sorting of month names can put April before February.

If you need to group or sort records by month, keep a real date in another column or use the real-date method below.

Create a real date for the month and year

To extract the month and year while preserving a usable date value, create the first day of the date's month:

=DATE(YEAR(A2),MONTH(A2),1)

For a date in A2 of June 18, 2026, the formula returns June 1, 2026.

Apply the custom number format mmmm yyyy to the result if you want it to display as June 2026.

Excel stores the result as a date, so you can sort it in calendar order, use it in date calculations, and group it in many PivotTable layouts.

The formula uses YEAR(A2) and MONTH(A2) to read the date's year and month, then DATE combines those values with day 1.

It works across year boundaries: December 20, 2026 becomes December 1, 2026, and January 5, 2027 becomes January 1, 2027.

Return the month and year separately

If you need numeric components for filters, helper columns, or other formulas, extract them separately.

With the date in A2, use:

  • =MONTH(A2) returns the month number, from 1 to 12.
  • =YEAR(A2) returns the four-digit year, such as 2026.

You can combine them into one numeric key with =YEAR(A2)*100+MONTH(A2).

For a date in June 2026, this returns 202606.

That key sorts in calendar order and can be helpful in summaries, though it is not itself a date.

To show the month name separately, use =TEXT(A2,"mmmm"); to return the year as text, use =TEXT(A2,"yyyy").

Which method should you use?

  • Use a custom number format when the original date should remain unchanged and only its display needs to show month and year.
  • Use TEXT when you need a text label for a report, message, or heading.
  • Use DATE(YEAR(...),MONTH(...),1) when you need a separate, sortable date representing the month.
  • Use MONTH and YEAR when separate numeric values are needed for calculations or filtering.

For monthly sales summaries or date-based charts, the first-day date is usually the most flexible helper value.

For a presentation-only label, formatting the original date avoids an extra formula column.

Excel and Google Sheets compatibility

The formulas TEXT, DATE, YEAR, and MONTH are available in current desktop versions of Excel, Excel for the web, and Google Sheets.

The examples use commas between function arguments; depending on regional settings, your spreadsheet may require semicolons instead.

Custom number formats are supported by Excel and Google Sheets, but the menus differ.

In Excel, use Format Cells and the Number tab's Custom category.

In Google Sheets, select the cells and choose Format, Number, then Custom date and time.

Enter or select a pattern that displays the month and year.

Month names in formulas and number formats can depend on the spreadsheet's locale.

If a workbook is shared across regions, abbreviations, month names, and date-entry conventions may vary.

The underlying date value remains the key to reliable date calculations.

Handle text dates and blank cells

These methods expect A2 to contain a valid date value.

A date that looks like 6/18/2026 may actually be text, especially if it was imported from a CSV or entered using a different regional date order.

If YEAR or MONTH returns an error, check whether Excel recognizes the cell as a date.

You may need to convert the imported text using Text to Columns, a date-parsing function, or a corrected regional format.

To leave the result empty when A2 is blank, wrap the formula in an IF check.

For a month-year text label, use:

=IF(A2="","",TEXT(A2,"mmmm yyyy"))

For a real date representing the month, use:

=IF(A2="","",DATE(YEAR(A2),MONTH(A2),1))

These checks prevent formulas from showing an unintended label or date for empty rows.

If a source cell contains an error rather than a blank, the error will still pass through; use IFERROR only if you also want to replace invalid-date errors with a chosen fallback, such as an empty string.