How to convert a date to month and year in Excel

Q: How do I only change how the date looks?

Give the cells the custom number format mmm yyyy. The full date stays in the cell.

Formula (Excel and Google Sheets)

=TEXT(A2,"mmm yyyy")

Use =TEXT(A2,"mmm yyyy") for a label like Jan 2026, or keep a real date with =EOMONTH(A2,-1)+1 and a "mmm yyyy" format.

Result on the example: one result per row in B2, filled down to B6
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

TEXT returns the date as text in the pattern you give it: "mmm" is the short month name and "yyyy" the four-digit year. It is the quickest way to make labels like Jan 2026 for a report or a chart.

=TEXT(A2,"mmm yyyy")

Result"Jan 2026"

A21/14/2026
The value in A2.
TEXT(A2,"mmm yyyy")"Jan 2026"
TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".
- valueA21/14/2026
- format_text"mmm yyyy"

In Google Sheets

The same formula works in Google Sheets: =TEXT(A2,"mmm yyyy"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=EOMONTH(A2,-1)+1	46023, 46082, 45992…Different result	Keeps a real date, the first of the month. Give it the custom format "mmm yyyy" to show Jan 2026; it sorts and groups correctly. A real date, not text.
=TEXT(A2,"yyyy-mm")	2026-01, 2026-03, 2025-12…Different result	Text that sorts in date order, like 2026-01. Shows 2026-01.
=TEXT(A2,"mmmm yyyy")	January 2026, March 2026, December 2025…Different result	The full month name: January 2026. Full month names.

Common mistakes

Text labels sort alphabetically: Apr 2026 comes before Jan 2026. For sorting and grouping, use the first-of-month date with a "mmm yyyy" number format.
TEXT month names follow the spreadsheet's language.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Convert a date to month and year

In B2, show the month and year of the date as text like "Jan 2026", and fill down to B6.

Questions

How do I sum by month and year?

Use the first of the month as a key, D2, and the month end: =SUMIFS(amounts,dates,">="&D2,dates,"<="&EOMONTH(D2,0)).

How do I only change how the date looks?

Give the cells the custom number format mmm yyyy. The full date stays in the cell.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.