Converting an Excel month name to a number is a common task when cleaning dates, building reports, or preparing data for analysis. This process helps standardize text-based months into numeric values that work better in formulas and pivot tables.
Whether you are working with full month names like "March" or abbreviations like "Mar", Excel provides multiple reliable approaches. The methods below cover formula solutions, helper columns, and easy reference tools.
| Method | When to Use | Formula Example | Notes |
|---|---|---|---|
| MONTH with DATE | Full month name in a known format | =MONTH(DATEVALUE(A1 & " 1")) | Returns 1–12, requires recognizable date text |
| XLOOKUP table | Custom order or handling abbreviations | =XLOOKUP(A1, keyRange, valueRange) | Flexible, easy to extend for fiscal months |
| SWITCH statement | Explicit control without a table | =SWITCH(A1, "Jan", 1, "Feb", 2, ... ) | Readable for small sets, no helper range needed |
| TEXT to columns + MONTH | Block of raw dates stored as text | =MONTH(TEXTVALUE(A1)) | Use after converting text to real dates |
Using MONTH and DATEVALUE for Full Names
This approach works well when your cells contain complete month names such as "April" or "November". The DATEVALUE function concatenates the month name with a day and year, creating a real Excel date. The MONTH function then extracts the corresponding number.
Wrap the text in concatenation with a space and a day value to ensure DATEVALUE recognizes it. This method respects regional date settings and avoids manual lookup tables when data follows standard English month names.
Building a Custom Lookup Table
When dealing with abbreviated codes or non‑Gregorian fiscal months, a custom lookup table gives you full control. You define two columns, one for the month label and one for the desired number, which keeps logic transparent and easy to audit.
XLOOKUP or INDEX MATCH can retrieve the correct number based on an exact or approximate match. This structure is ideal for dashboards where month order might differ from the calendar, or where additional metadata such as period status is needed.
Using SWITCH for Compact Formulas
The SWITCH function offers a lightweight alternative when you do not want a separate reference table. It tests the month name and returns a numeric result directly within a single formula, which is helpful for quick ad‑hoc models.
While slightly longer than a lookup, SWITCH avoids volatile behavior and remains easy to read. You can nest multiple conditions in a predictable order, reducing the risk of ambiguous matches in messy source data.
Handling Text‑to‑Columns Dates
Sometimes dates are stored as text and do not respond to normal date functions. Using Text to Columns forces Excel to parse the strings as actual date values, after which MONTH works normally.
Specify a consistent delimiter such as space or slash and match column data type to general or date. This preprocessing step prevents errors when source files come from external systems with inconsistent formatting.
Key Takeaways for Month Name Conversion
- Use DATEVALUE + MONTH for standard English month names in full form.
- Build a small lookup table with XLOOKUP for abbreviations or fiscal calendars.
- Choose SWITCH for compact formulas when the list of months is small and fixed.
- Preprocess misformatted text with Text to Columns to unlock native date functions.
- Validate locale settings when working with non‑English month names.
- Test edge cases such as leading spaces, mixed case, and non‑breaking characters.
FAQ
Reader questions
How do I convert a month abbreviation like "Jun" to a number without a helper table?
Use a SWITCH formula that explicitly lists each abbreviation and its corresponding number, or embed a small XLOOKUP referencing a two‑column range that maps abbreviations to values.
What if my month names are in a language other than English?
Excel date functions rely on system locale, so ensure regional settings match the language. Otherwise, build a translation table in the lookup approach and reference it with XLOOKUP or INDEX MATCH.
Can I handle both full month names and abbreviations with one formula?
Yes, combine LEN to detect the input length, then route to either a DATEVALUE path for long names or a SWITCH/XLOOKUP path for short codes, ensuring consistent numeric output.
Why does my MONTH formula return errors when the source seems correct?
Check for extra spaces, non‑breaking characters, or mismatched date formats. Use TRIM and CLEAN, verify that DATEVALUE can interpret the text, and ensure referenced cells are not formatted as text.