How to convert a month name to a number in Excel

Method 1: MONTH and DATEVALUE

A sales report exported from another system lists the month as text: "March", "Mar", "JANUARY", and a few cells someone typed by hand. MONTH needs a real date, so =MONTH(A2) on the text "March" returns #VALUE!. The trick is to turn the name into a date first.

A2&" 1" builds the text "March 1". DATEVALUE reads that as March 1 of the current year, and MONTH pulls out the month number. The year doesn't matter here, because you only keep the month.

It works for full names and standard short names, in any capitals: "Mar" gives 3, "Dec" gives 12 and "JANUARY" gives 1. It fails on non-standard abbreviations: "Sept" in A5 returns #VALUE!, because Sept is not one of the short names DATEVALUE reads. Method 2 handles that.

Method 2: MATCH the first three letters (handles messy text)

This version doesn't ask Excel to understand dates at all. It looks up the first three letters in a list of the twelve months, and the position in the list is the month number. "Sept" starts with "Sep", the 9th item, so it returns 9. MATCH ignores capitals, so "JANUARY" still gives 1.

TRIM matters more than it looks. The July in A8 was exported with two spaces in front, so LEFT(A8,3) grabs " Ju" and MATCH returns #N/A. With TRIM it gives 7. The trailing space after "september " in A4 doesn't hurt, because LEFT takes only the first three letters anyway.

The list in curly braces is an array constant: no helper table needed. If your data is in another language, write the list in that language. That is also the safer choice when you share the file, because DATEVALUE reads month names in the language of each person's regional settings.

Convert the whole column at once

In Excel 365 and Excel 2021 or later, give either formula the whole range and it spills one number per row. With Method 2 every row comes back clean: 3, 3, 9, 9, 12, 1 and 7. The DATEVALUE version would show #VALUE! for the Sept row, which is a quick way to spot the odd entries in a big export.

In older Excel, enter the formula in D2 with A2 and fill it down. If you add a column for the number, give it a header like Month no. so nobody confuses it with a day.

Build a real date from month and year columns

Often you don't want just the number. You want a real date, so you can sort, filter, use SUMIFS by date range or make a chart axis. Feed the month number into DATE with the year column: =DATE(B2, month number, 1) gives Mar 1, 2026.

The Dec row belongs to 2025, and DATE uses that year: it returns Dec 1, 2025. A text version also works for clean names: =DATEVALUE("1 "&TRIM(A6)&" "&B6) builds "1 Dec 2025" and returns the same date (it shows as 45992 until you format the cell as a date).

Need the last day of that month, for a "month ending" column? Wrap it in EOMONTH(...,0), which turns Dec 1, 2025 into Dec 31, 2025.

Sort month names in calendar order, not A to Z

Sort a column of month names and Excel sorts them alphabetically: =SORT(A2:A8) puts the padded " July" first, then Dec, JANUARY, Mar, March, Sept and september. That's almost never what you want.

Sort by the year, then by the month number, without adding a helper column. SORTBY takes the range to sort and then pairs of "sort by this, in this order". The result runs Dec 2025, JANUARY, March, Mar, July, september and Sept.

Once you have month numbers, totals are easy too. =SUMPRODUCT((MATCH(LEFT(TRIM(A2:A8),3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)<=3)*(B2:B8=2026)*C2:C8) adds the January to March 2026 sales and returns 11615. If you want month names to sort correctly with the ribbon instead, use Data > Sort, pick the column, and set Order to Custom List, then choose the January, February, March list.

Stuck on your own export? Paste a few rows into Cellwise Fix (cellwise.app/excel-formula-helper/) and it writes the formula for your exact columns, checked on your data.