Working with dates in Excel often requires extracting specific components, such as the month, for reporting, analysis, or data visualization. Knowing how to pull the month from a date can save time and simplify calculations.
In this article, we’ll explore six practical methods to extract the month from a date in Excel, ranging from simple formulas like MONTH and TEXT to more advanced functions like CHOOSE and SWITCH, custom formatting, and Power Query. Let’s get started.
Steps to extract month from date in Excel:
➤ In a blank column (E2), type: =TEXT(B2,”mmmm”)
➤ Press Enter.
➤ Drag the formula down to fill all rows.
Display Month Using Custom Number Formatting
When you want to display only the month from a date without creating a new column or formula, custom number formatting is a convenient solution. It keeps the original date intact but shows only the month visually. This is especially useful in dashboards, reports, or tables where you want a clean, readable month view without altering your data.
We’ll use the following dataset where we will extract the month from the dates in column B:
Steps:
➤ Select the column or cells with dates (B2:B11).
➤ Press Ctrl + 1 to open Format Cells.
➤ Go to the Number tab >> select Custom.
➤ In the Type box, type: mmmm
➤ Click OK to apply.
Your cells will now display only the month name while retaining the original date values.
Convert Date to Month Name Using the TEXT Function
When your goal is to display the month as text, for example “January” or “Feb,” the TEXT function is perfect. It converts dates into readable text strings and is useful when creating reports, labels, or combining month names with other text in Excel.
Steps:
➤ Go to a blank cell like E2.
➤ Type the following formula:
=TEXT(B2,"mmmm")
➤ Press Enter for output.
➤ Drag the formula down to fill all rows.
This returns the full month name for each date in your dataset.
Extract Numeric Month Using the MONTH Function
Sometimes you need the month as a number for calculations, sorting, or pivot tables. The MONTH function converts a full date into a numeric value from 1 to 12, making it ideal for analysis or when working with formulas that rely on numeric month values.
Steps:
➤ Go to a blank cell like E2.
➤ Type the following formula:
=MONTH(B2)
➤ Press Enter for output.
➤ Drag the formula down to fill all rows.
Each date will return a number between 1 and 12. For example, 02/15/2024 becomes 2.
Return Month Names Using the CHOOSE Function
If you want full control over the text returned for each month, the CHOOSE function is a great option. By converting numeric month values to custom names or abbreviations, you can format months exactly as you want. This method is particularly useful when creating concise tables, charts, or dashboards.
Steps:
➤ Go to a blank cell like E2.
➤ Type the following formula:
=CHOOSE(MONTH(B2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
➤ Press Enter for output.
➤ Drag the formula down to fill all rows.
Each date will display the corresponding abbreviated month name.
Get Month Names with the SWITCH Function
For larger datasets or when you want a clean, readable formula without nested IF statements, the SWITCH function works efficiently. It returns numeric month values to text names in a straightforward way, making your formulas easier to read and maintain while returning accurate month names.
Steps:
➤ Go to a blank cell like E2.
➤ Type the following formula:
=SWITCH(MONTH(B2),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")
➤ Press Enter for output.
➤ Drag the formula down to fill all rows.
Now each date will show the full month name as text.
Make Use of Power Query to Extract Month
When working with large datasets or multiple date columns, Power Query offers a no-formula solution. It allows you to extract months quickly, transform data, and load it back into Excel. This is ideal for automated workflows, reports, or tables that frequently update.
Steps:
➤ Go to A1 cell or select your dataset >> Data >> Get & Transform >> From Table/Range.
➤ Confirm your range, headers and click OK.
➤ In Power Query Editor, select the Joining Date column.
➤ Go to Add Column >> Date >> Month >> Name of Month.
➤ Click Close & Load from Home tab to bring the updated table back into Excel.
➤ A new column with the month name will appear on a new sheet.
Downloadable Resources
Get our practice workbook or necessary files that we have worked with to prepare this article.
Frequently Asked Questions
Can I extract the month from a date without using formulas?
Yes, you can use Custom Number Formatting to display only the month. This method changes how the date looks without altering its value, making it useful for reports where formulas are unnecessary.
How do I get the month as a number instead of text?
Use the MONTH function, which returns a numeric value from 1 to 12. This is ideal for calculations, sorting, or pivot tables where numeric month values are required rather than month names.
Can I create abbreviated month names in Excel?
Yes, functions like CHOOSE or TEXT(B2,”mmm”) allow you to display abbreviated month names (e.g., Jan, Feb). This is helpful for compact reports or charts where full month names are too long.
Is there a method to extract months for large datasets without formulas?
Power Query provides a no-formula solution for extracting months from dates in large tables. It is scalable, updates dynamically, and lets you transform and load the month column back into Excel efficiently.
Will the extracted month update automatically if the date changes?
Yes, using formulas like MONTH, TEXT, CHOOSE, or SWITCH, the extracted month updates automatically whenever the original date changes, ensuring your analysis or reports always reflect the current data accurately.
Wrapping Up
In this tutorial, we explored six practical methods to extract the month from a date in Excel, including numeric and textual outputs. Depending on your needs, you can choose between formulas, custom formatting, or Power Query for dynamic and efficient results. Feel free to download the practice file and share your feedback.
