To show the month of a date as text in Excel, use the TEXT function with a format code like "mmm" or "mmmm". For example, if cell A1 contains 2024-05-14, the formula =TEXT(A1,"mmmm") returns May, and =TEXT(A1,"mmm") returns May (abbreviated). This is the quickest and most flexible method, but there are several alternatives depending on whether you need a static value, a sortable month number, or a formula that works in older Excel versions.
Why Convert a Date to Month Text?
Excel stores dates as serial numbers (e.g., 1 = January 1, 1900), which is great for calculations but not for human-readable reports. When you need to group sales by month, label a chart axis, or create a summary table, showing “January” instead of “01/15/2024” makes the data instantly understandable. Converting to text is also useful when you want to concatenate the month with other text, like “Sales – March”. However, remember that once you convert a date to text, it is no longer a real date – you lose the ability to sort chronologically or use it in date arithmetic unless you also keep the original date column.
The TEXT Function: The Standard Solution
The TEXT function converts a number or date to text using a format code. The syntax is =TEXT(value, format_text). For dates, common month format codes are:
"mmm"– abbreviated month name (Jan, Feb, Mar)."mmmm"– full month name (January, February, March)."mmm yyyy"– month and year (Jan 2024)."mmmm d, yyyy"– full month, day, and year (January 15, 2024).
For example, with the date in A1, =TEXT(A1,"mmmm") returns the full month name. The result is text, so you cannot use it in date calculations, but you can use it in text concatenation or as a lookup value. Note that the TEXT function is locale-sensitive: if your Excel is set to a non-English language, the month names will appear in that language. To force English, you can use a custom VBA function or the CHOOSE approach below.
Using CHOOSE and MONTH for a Static Month Name
If you want a formula that returns a month name without using TEXT (for example, to avoid locale issues or to have a result that is not affected by cell formatting), you can combine MONTH and CHOOSE. The MONTH function returns the month number (1–12) from a date. Then CHOOSE picks the corresponding name from a list. With the date in A1, the formula is:
=CHOOSE(MONTH(A1),"January","February","March","April","May","June","July","August","September","October","November","December")
This always returns English month names regardless of your system language. It is a bit longer but fully predictable. A shorter alternative is to use SWITCH (available in Excel 2016 and later), but CHOOSE is more widely compatible.
Converting to Text with Power Query
If you are working with large datasets and want to transform the data permanently, Power Query (Get & Transform) is a robust option. To convert a date column to month text in Power Query:
- Select a cell in your table and go to Data > From Table/Range (or Data > Get & Transform > From Table/Range).
- In the Power Query editor, select the date column.
- Go to Add Column > Date > Month > Name of Month. This adds a new column with the full month name as text.
- Optionally, you can also choose Short Month for the abbreviated name.
- Click Close & Load to bring the transformed data back to Excel.
This method is ideal when you need to refresh the data regularly, as the transformation steps are saved and re-applied on refresh. Note that the month name will follow the operating system’s language, but you can change the column’s culture in Power Query if needed.
Formatting vs. Converting: What’s the Difference?
A common misconception is that changing the number format to display the month name converts the date to text. That is not true. For example, if you select a date cell and go to Home > Number Format > More Number Formats, then choose a custom format like mmmm, the cell will display “May”, but the underlying value remains a date serial number. The cell still contains a real date, so you can sort it chronologically and use it in formulas. To actually convert to text, you must use a formula like TEXT or paste as values. If you only need the display to change, use formatting; if you need the text to be used in other text operations, use conversion.
Common Problems and How to Avoid Them
Several issues can arise when converting dates to month text:
- Locale issues:
TEXTuses your system’s language. To force English, useCHOOSEor a custom VBA function. - Sorting problems: If you convert to text, alphabetical sorting puts April before January. To keep chronological order, either keep the original date column or create a helper column with the month number.
- Text that looks like a date: If you type “May” in a cell, Excel may interpret it as a date if you later use it in a formula. To avoid this, format the cell as Text before typing, or use an apostrophe before the text.
- Leading zeros: If you use a format like
mm(e.g.,=TEXT(A1,"mm")), you get “05” for May, which is text, not a number. This is useful for sorting but can be confusing.
Frequently Asked Questions
How do I get the month name from a date in Excel without a formula?
You can use the Format Cells dialog (Ctrl+1) and apply a custom number format like mmmm. This changes the display only, not the underlying value. For a true text result without a formula, you can copy the date cells, paste them as values, then apply the format – but the result is still a date serial number, just displayed as text. To get actual text, you must use a formula or Power Query.
Why does my TEXT function return a number like 5 instead of “May”?
If you use =TEXT(A1,"m"), you get the month number as text (e.g., “5”). To get the name, you must use "mmm" or "mmmm". The single m is for the month number, not the name.
Can I convert a date to month text in Google Sheets the same way?
Yes, Google Sheets has a similar TEXT function, but the format codes are slightly different. For example, =TEXT(A1,"MMMM") returns the full month name. The CHOOSE and MONTH approach also works in Google Sheets. However, Power Query is not available in Google Sheets; instead, you can use the MONTH and CHOOSE functions or the TO_TEXT function (which does not format).
How do I convert a date to the month name and keep it sortable?
Create a helper column with the month number using =MONTH(A1), then sort by that helper column. Alternatively, use a custom number format that displays the month name but keeps the date value, so sorting works naturally. If you need text, you can use =TEXT(A1,"mmmm") but then sort by the original date column.
Is there a way to get the month name from a date without using the TEXT function?
Yes, you can use =CHOOSE(MONTH(A1),"January",...) as described above. Another option is to use the SWITCH function (Excel 2016+) or a custom VBA function. For a non-formula approach, use Power Query’s Date > Month > Name of Month.
Alternatives and When to Use Them
If you need the month name for a chart label, consider using the date’s number format instead of converting to text – this keeps the date intact and allows proper axis scaling. If you need to group by month in a pivot table, right-click the date field and select Group, then choose Months – this groups without converting to text. If you need a static text value for a report, use TEXT or CHOOSE and then copy-paste as values to remove the formula. For dynamic dashboards, consider using the XLOOKUP function with a month number to name mapping table, which is easy to maintain and avoids long formulas.
Related
- Excel Subtotals: How to Add Them Step by Step
- Excel Hourly Schedule Template: How to Make One
- Excel: How to Add Lines in a Cell (3 Easy Ways)
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Signature Image in Word Without Blurry EdgesTo insert a signature image in Microsoft Word, place the cursor where the signature should appear, choose Insert…
- How to Open JSON in Excel and Turn It Into a TableTo open a JSON file in Excel for Microsoft 365 on Windows, select Data > Get Data >…
- Excel Flash Fill for an Entire ColumnYes. In Excel for Microsoft 365 on Windows, enter one example in the first cell of an empty…
- How to Insert a Horizontal Line Under a Heading in WordTo add a line beneath a heading in Microsoft Word, click in the heading paragraph, then choose Home…
- COUNTIF Google Sheets Date Range: Formula, Examples & TipsTo count dates within a range in Google Sheets, use COUNTIFS with a start-date condition and an end-date…
- Excel NOW Format: Show the Current Date and Time CorrectlyIn Excel, enter =NOW() in a cell to return the current date and time; then format that cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.