Explanation
In this example, the goal is to get and display the month name from any given date. There are several ways to go about this in Excel, depending on whether you want to extract the month name as text, or just display a valid Excel using the month name.
To extract the month name from a date as text, you can use the TEXT function with a custom number format like "mmmm", or "mmm". In the example shown, the formula in cell C4 is:
=TEXT(B4,"mmmm") // returns "April"
The TEXT function converts values to text using the number format that you provide. Note that the date is lost in the conversion: only the text for the month name remains. To extract an abbreviated month name like "Jan", "Feb", etc. use a slightly different number format:
=TEXT(B4,"mmm") // returns "Apr"
Display month name
If you only want to display a month name, you don't need a formula – you can use a custom number format to format the date directly. Select the date and navigate to Format cells (Ctrl + 1 or Cmd +1), then select Custom and enter one of these custom formats:
"mmm" // "Jan"
"mmmm" // "January"
Excel will display only the month name, but it will leave the date value intact. You can also use a longer date format like this:
"mmmm d, yyyy" // "January 1, 2021
CHOOSE option
For more flexibility, you create your own month names with the CHOOSE function and MONTH function like this:
=CHOOSE(MONTH(date),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Enter the month names you want to return (customized as you like) as values in CHOOSE, after the first argument, which is entered as MONTH(date). The MONTH function will extract a month number, and CHOOSE will use this number to return the "nth" value in the list. This works because MONTH returns a number 1-12 that corresponds to the month name.
CHOOSE is more work to set up, but it is also more flexible, since it allows you to map a date to any values you want (i.e. you can use values that are custom, abbreviated, not abbreviated, in a different language, etc.).
Related formulas
Get month from date
Get day name from date
Convert date to text
Convert text to date
Related functions
MONTH Function
The Excel MONTH function extracts the month from a given date as a number between 1 and 12. You can use the MONTH function to extract a month number from a date into a cell or to feed a month number into another function like the DATE function....
CHOOSE Function
The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.
TEXT Function
The Excel TEXT function returns a number formatted as text. This makes it easy to embed a formatted number (e.g., dates, times, currencies, percentages, fractions, etc.) in a text string with the number format of your choice.
