# How to convert a date to month and year in Excel

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.

**Excel:** `=TEXT(A2,"mmm yyyy")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date | Month |
| 2026-01-14 |  |
| 2026-03-02 |  |
| 2025-12-31 |  |
| 2026-07-04 |  |
| 2026-11-19 |  |

Formula in B2, filled to B6: `=TEXT(A2,"mmm yyyy")`

Result: Jan 2026; Mar 2026; Dec 2025; Jul 2026; Nov 2026

## 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.

## Other ways

- `=EOMONTH(A2,-1)+1`: 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")`: Text that sorts in date order, like 2026-01. (Shows 2026-01.)
- `=TEXT(A2,"mmmm yyyy")`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-a-date-to-month-and-year