How to Convert a Date to a Month in Excel

To convert a date to a month in Excel, use =MONTH(A2) to get the month number (10 for October), or =TEXT(A2,"mmmm") to get the month name (October). Use "mmm" for the short name (Oct). If you only want the cell to show the month while keeping the full date underneath, for sorting and calculations, apply the custom number format mmmm instead. Here’s when to use each, plus how to total data by month.

Quick reference

You want	Formula or format	Result for 10/8/2026
Month number	=MONTH(A2)	10
Full month name	=TEXT(A2,"mmmm")	October
Short month name	=TEXT(A2,"mmm")	Oct
Month and year	=TEXT(A2,"mmmm yyyy")	October 2026
First day of the month	=DATE(YEAR(A2),MONTH(A2),1)	10/1/2026
Show the month, keep the date	Custom format mmmm	October (still a date)

Method 1: Month number with MONTH

1. In B2, type =MONTH(A2) and press Enter.
2. Double-click the fill handle to copy it down.

The result is a number from 1 to 12, which is useful for formulas such as =COUNTIF(B:B,10) to count October entries.

Method 2: Month name with TEXT

=TEXT(A2,"mmmm") returns the full name, and "mmm" returns the three-letter abbreviation. The result is text, so it can’t be sorted in calendar order. Excel sorts it alphabetically (April, August, December…). Use Method 3 or 4 when order matters.

Turn a month number into a name

If B2 holds a number from 1 to 12: =TEXT(DATE(2026,B2,1),"mmmm"). Any year works, since only the month matters.

Method 3: Show the month with a custom format

1. Select the dates.
2. Press Ctrl + 1 and choose Custom.
3. Type mmmm (or mmm yyyy) in the Type box and click OK.

The cells display only the month, but each one is still the original date. Sorting, filtering and date maths keep working. For more custom codes, see how to change the number format in Excel.

Method 4: Total or count by month

PivotTable grouping

1. Click in your data and go to Insert > PivotTable > OK.
2. Drag the date field into Rows. Recent versions of Excel group it into Years and Months automatically.
3. If they don’t, right-click a date in the PivotTable, choose Group, and select Months (and Years).
4. Drag the amount field into Values.

See how to make a pivot table in Excel.

Formulas

To total sales in column C for October 2026: =SUMIFS(C:C,A:A,">="&DATE(2026,10,1),A:A,"<"&DATE(2026,11,1)). Using date ranges like this is more reliable than matching month names, and it keeps different years separate.

Related conversions: quarter, year and fiscal month

• Quarter: ="Q"&ROUNDUP(MONTH(A2)/3,0) gives Q4 for October.
• Year: =YEAR(A2) gives 2026.
• Year and month as one sortable value: =TEXT(A2,"yyyy-mm") gives 2026-10. Because the year comes first, it sorts correctly as text.
• Fiscal month, for a year starting in July: =MONTH(EDATE(A2,-6)) returns 1 for July and 4 for October. Change -6 to match your fiscal year’s start.
• Last day of the month: =EOMONTH(A2,0) gives 10/31/2026.

These are handy as helper columns before building a PivotTable or chart, so you can group by quarter or fiscal period as well as by month.

Month names in other languages

TEXT uses your system’s language for month names. To force a language, add a locale code, for example =TEXT(A2,"[$-fr-FR]mmmm") for octobre.

Troubleshooting

Every row shows January

The cells probably contain text or numbers, not real dates. Excel treats an empty or zero cell as January 1900. Check that the dates are right-aligned, and convert text dates with Data > Text to Columns > Finish.

#VALUE! error

The source cell is text that Excel can’t read as a date. Retype it, or use =MONTH(DATEVALUE(A2)).

The month names sort alphabetically

Sort by the original date column, or by a MONTH number column, instead of the name column.

Related guides

• How to add 6 months to a date in Excel
• How to add dates in Excel

Summary

1. =MONTH(A2) for the number.
2. =TEXT(A2,"mmmm") or "mmm" for the name.
3. A custom mmmm format to show the month and keep the date.
4. A PivotTable or SUMIFS to total by month.