Excel Date Month and Year Only: Format or Convert?

How-to basics · Updated November 18, 2026

To display an Excel date as month and year only, select the date cells, press Ctrl+1, choose Number > Custom, and enter mmmm yyyy. This changes how the date looks without removing its day or changing the underlying date value; use a formula such as =TEXT(A2,"mmmm yyyy") only when you specifically need month and year as text.

Format a date to show month and year

In Excel for Microsoft 365 on Windows, apply a custom number format to the cells:

  1. Select the date cells or the entire date column.
  2. Press Ctrl+1 to open Format Cells.
  3. On the Number tab, select Custom.
  4. In the Type box, enter mmmm yyyy, then select OK.

A date such as June 15, 2026, will display as June 2026. Its day is hidden, not deleted. To use an abbreviated month, enter mmm yyyy, which displays Jun 2026. For a numeric style, use mm/yyyy for 06/2026 or yyyy-mm for 2026-06. These are display formats, not formulas.

You can also open the format options from the ribbon: select Home > Number Format drop-down > More Number Formats, then choose Custom. On a Mac, select the cells and press Command+1 to open Format Cells; choose Custom under Number and enter the same format code. In Excel for the web, use the Number Format controls on the Home tab and look for the custom format options. The exact layout can differ from the desktop app.

What Excel stores when a date is formatted

Excel normally stores a date as a number that represents a day in its date system. The number format tells Excel how to display that value. As a result, formatting a cell as mmmm yyyy does not turn the date into a month name or erase its day. If A2 contains June 15, 2026, it remains a full date that Excel can use in date calculations, even while the cell displays only June 2026.

This distinction matters when you sort, filter, calculate elapsed time, or use the date in another formula. Two cells containing June 2 and June 28 can both display as June 2026, but they are still different dates. The formatting hides that difference on screen. Clicking a cell and checking the formula bar can help reveal the full date value.

A custom format is usually the best choice when the goal is simply to make a report or worksheet easier to read. It keeps the original data intact and is reversible: apply a different date format whenever you want to show the day again.

Choose between a format and a formula

Pick the approach based on what the result needs to do, not just how it should look:

  • Use a custom number format when you want a date to look like June 2026 but still behave like a date. This is generally best for tables, charts, sorting, filters, and date calculations.
  • Use TEXT when you need a text label such as June 2026 for a heading, a concatenated sentence, or a system that specifically expects text. The result is text, not a date.
  • Use a normalized date when you want every date in a month represented by its first day, while retaining a true date value. You can format that result to show only the month and year.

For example, a monthly sales report can keep individual transaction dates and display them as month and year, or it can create a separate month-start date for grouping. Avoid replacing source dates with text unless you have a reason: text labels are less convenient for chronological sorting and date arithmetic.

Use formulas when you need a month-year result

With a valid date in A2, enter this formula in another cell to return a text label:

=TEXT(A2,"mmmm yyyy")

If A2 contains June 15, 2026, the formula returns the text June 2026. Fill the formula down to create labels for other rows. Use =TEXT(A2,"mmm yyyy") for a shorter label such as Jun 2026. The TEXT function uses a number format code, but its result is text; a value like June 2026 will not act as a date in date arithmetic.

If you need a genuine date representing the first day of the month, and A2 contains a valid date, use:

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

For June 15, 2026, this returns June 1, 2026. Apply the mmmm yyyy custom format to the result cell to display June 2026 while preserving a usable date value. This is useful for monthly summaries because every date in June can be mapped to the same month-start date. The formula does not itself hide the day; formatting does that.

Sorting, filtering, and monthly summaries

With a custom format, Excel sorts the underlying dates, not the visible month-year labels. That is usually what you want for a chronological list. If all dates in a month are displayed identically, their order within that month may not be obvious, but Excel still retains it. A filter may also show repeated-looking values because multiple full dates share the same display.

For a monthly summary, consider grouping the date field in a PivotTable or using a month-start date in a helper column. In a PivotTable, right-click a valid date in the date field and choose Group, then select Months and Years as appropriate. Including Years is important when the source covers multiple years: grouping by month name alone can combine January from different years. PivotTable grouping requires actual date values; text labels created with TEXT may not group as dates.

In a chart, a date axis can retain chronological spacing even if its labels display only the month and year. If a chart or report needs one category per month, first aggregate the records by month; changing the label format alone does not combine separate records.

Common problems and how to fix them

  • The format has no effect. The cells may contain text that looks like a date rather than real Excel dates. Changing a number format cannot convert text. Try converting the entries to dates, for example with Text to Columns or a suitable date conversion process, then apply the custom format.
  • The result shows a number. The cell may be formatted as General or Number. Reapply a date format such as mmmm yyyy. A serial number can also indicate that Excel recognizes a date but is displaying its underlying numeric value.
  • The month appears as minutes. In Excel format codes, the letter m can mean month or minute depending on context. Use the clear code mmmm yyyy for a full month followed by a four-digit year. If a format is part of a time value, m may be interpreted as minutes; check that the cells contain dates and use a date-only code.
  • The month name is in an unexpected language. Month names can reflect regional settings. If the workbook is opened on a computer with different locale settings, names may display differently. Excel supports locale-specific format codes, but the code syntax and available options can depend on the installed language and region.
  • A blank or invalid input gives an odd result in a formula. TEXT and date formulas expect a valid date value. Check for blank cells, imported text, or errors in the source column before filling a formula down. A formula that refers to an empty cell may produce a date-related result that is not meaningful for your report.

Examples of useful month-year formats

Choose a format that fits the audience and the space available. The underlying date stays the same when you switch among these display choices:

  • mmmm yyyy displays June 2026. This is clear in a report or printed table.
  • mmm yyyy displays Jun 2026. It is more compact while remaining easy to read.
  • mm/yyyy displays 06/2026. This is concise, but readers must understand the numeric month convention.
  • yyyy-mm displays 2026-06. This year-first style is useful in labels that need to sort naturally as text, though the original date remains numeric when applied as a format.

For a column heading, a formula can create a text label, but a date formatted as mmmm yyyy can also be used directly if the heading cell contains a date. If you use a formula to combine a date with other text, remember that Excel may display the date as its serial number unless you apply TEXT to the date part. Keep the source date column where possible so readers can inspect or calculate with the original values.

Frequently Asked Questions

How do I show only the month and year in Excel without changing the date? Select the date cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and apply the custom format mmmm yyyy. The displayed day disappears, but the original date remains available.

How do I make Excel display Jun 2026 instead of June 2026? Apply the custom number format mmm yyyy. If you need a text result instead, with the date in A2, use =TEXT(A2,"mmm yyyy"); that formula returns text.

Can I sort dates formatted as month and year? Yes. A custom number format leaves the underlying values as dates, so Excel can sort them chronologically. A TEXT formula returns labels, which may sort alphabetically rather than in date order.

Why does my month-year formula return a number or an error? Check that the referenced cell contains a valid Excel date rather than text, and that the formula is entered correctly. A custom format can show a date serial as a date, but it cannot repair an invalid or text value by itself.

How can I group dates by month and year in a PivotTable? Start with a source column containing real Excel dates. Right-click a date in the PivotTable, select Group, and choose Months and Years. Choose both when the source includes more than one year to keep, for example, January 2025 separate from January 2026.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.