Date & Time
Convert month name ("April" or "Apr") to month number (1–12)
Match the first 3 letters of any English month name against a 12-month array constant in XMATCH for locale-independent 1–12 numbers.
Why XMATCH on 3-letter prefixes beats DATEVALUE("1-" & A2)
The classic trick MONTH(DATEVALUE("1-" & A2 & "-2026")) relies on regional date-string parsing and breaks when opened on non-English OS locales.
Every English month has a unique 3-letter prefix ("Jan" through "Dec"). Matching LEFT(TRIM(A2), 3) inside XMATCH handles full names ("September"), abbreviations ("apr"), and mixed capitalization identically everywhere.
| MonthText (A) | 3-Letter Prefix | Month Number | 2026 Start Date |
|---|---|---|---|
| September | Sep | 9 | 2026-09-01 |
| apr | apr | 4 | 2026-04-01 |
Month-number and calendar-date formulas
Wrap XMATCH in IFNA(..., "Invalid month") to flag non-month headers like "Total" or "Q1".
=IFNA(XMATCH(LEFT(TRIM(A2), 3), {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}), "Invalid month")Display the first-of-month date as text
DATE creates a numeric date; the outer TEXT call here returns a display string. Omit TEXT and apply a cell date format when further date arithmetic is needed.
=TEXT(DATE(2026, XMATCH(LEFT(TRIM(A3), 3), {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}), 1), "yyyy-mm-dd")Inputs and interpretation
This is an English three-letter-prefix parser, not full input validation: Marching also matches Mar. Validate against an approved list of full names and abbreviations if arbitrary text is allowed. It does not translate French, Bulgarian, or other month names.
Try the example
Use the original values or change them to test your understanding.