Excel allows you to quickly extract the month from a given date using built-in functions such as MONTH and TEXT. This is one of the common tasks in Excel, and you can extract months from thousands of dates within a few seconds.
Whether creating reports, managing schedules, or analyzing trends, knowing how to get the month from a date can be incredibly useful. It is crucial as we often have to perform calculations based on it to prepare specific reports demonstrating task progress, deadlines, or order counts.
In this article, we’ll explore how to extract month from date in Excel. Make sure to read the article to the end, as it contains useful instructions that may help you in the future when handling dates.
Here are the two methods to extract the month from a given date. You can click on the respective links to jump to that particular section.
Download the Example Excel Sheet
Please consider downloading the following Excel spreadsheet to practice the functions and methods we will discuss in the following sections.
It contains the datasets used to demonstrate this article’s MONTH and TEXT functions. If you have your own dataset ready, you can skip downloading the file.
Explained: The MONTH function in Excel
The MONTH function extracts the month for a given date. It returns an integer value representing a month, ranging from 1 to 12.
The general syntax for the MONTH function is as follows,
=MONTH(serial_number)Where “serial_number” is any valid date from which you wish to extract the month. You must ensure that the date format is compatible with Excel. You can enter the date in double quotations or the cell reference containing the date.
For Example, consider the following formula,
=MONTH(“5/22/2002”)It should return the number “5” as a result.
Explained: The SWITCH Function in Excel
The SWITCH function in Excel compares an expression against a list of values and returns the result according to the first matching value.
The General syntax for the SWITCH function is as follows,
=SWITCH(expression, value1, result1, [default or value2, result2],..[default or value3,result3])Where,
- “expression” – It is the required argument which needs to be replaced with a logical expression, formula, or equation
- “valueN” – It is the value compared against the expression
- “resultN” – Here, you need to specify the expected output if the value matches the output of the expression
- “default” – It is the value returned if no matches are found in the ValueN expressions
The SWITCH function is an array formula that performs multiple calculations to obtain the desired output. Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
How to Get Month from Date using the MONTH function in Excel
Microsoft Excel provides a dedicated MONTH function to extract a month from the specified date. The MONTH function will return 1 for January, 2 for February, 3 for March, and so on.
If you want the number of the current month, then use the formula,
=MONTH(TODAY())Though these integer values are helpful in a few cases, you may be particularly interested in month names. Do not worry; we will also learn to convert those integer month numbers to their respective month names.
Task: To get the month numbers from each employee’s hiring date. We have 20 records that need to be organized by Month.
Let’s divide the process of getting a month from a given date into two steps.
STEP #1 – Extract Month (Number) from a given Date
In this step, we will use the MONTH function in Excel to get an integer value for the month from a given date.
Before that, we will add two columns, “Month Number” and “Month Name”, to get the results shown in the following image.
Here are the steps,
- Open the desired Excel sheet
- Click on the first cell below the header “Month Number”
- Type “=MONTH”
- Select the first option from the popup or press “Tab” on your keyboard
- In place of the “serial_number” argument, enter the cell reference as “B2”
- End the formula using “)”
- Press the “Enter” button on your keyboard
The final formula would look like the following,
=MONTH(B2)After following the above steps, Excel will instantly return the month for the first date in B2.
Using the Fill Handle, you can apply the above formula to the rest of the cells. You need to hover to the lower-rightmost corner of the cell “C2” (where we have used the MONTH function) until you see an “+” icon. Next, click on that icon and simply drag the formula to the end of the table.
PRECAUTION – How to deal with the #VALUE! Error
If you encounter a “#VALUE!” error, it should probably be due to the invalid date format you entered. Make sure to use the proper format for all dates.
STEP #2 – Converting the Numbers to Date using the SWITCH function
As discussed earlier, the MONTH function returns an integer value representing the month. Our next task is to convert that number to the month’s name.
We will use the SWITCH function to achieve this task.
Our task is to convert the numbers obtained by the MONTH function to month names. For example, “1” to “January”, “2” to “February”, and so on.
Here are the steps,
- Select the first cell below the header “Month Name”
- Type “=SWITCH”
- Select the first option from the popup or press “Tab” on your keyboard
- For the “expression” argument, we will use the MONTH function by providing the cell reference value as “C2”
- Press “,” on your keyboard to move to the following argument
- The condition here will be to convert “1” to “January”. So, we will put “1” in the place of the “value1” argument
- Press “,” on your keyboard
- Type “January” to replace the “result1” argument
(Make sure to put the January in double quotation marks; otherwise, the SWITCH function will return an error) - Press “,” to move to the next argument
- Now, for the second condition of converting “2” to “February”, type “2” in the place of the “value2_or_default, …” argument
- Type “February” to replace the “result2, …” argument, and make sure to use the double quotations
- Press “,” on your keyboard
- Repeat the steps 6 to 8 for the rest of the months from March to December
- End the formula with closing parenthesis “)”
- Press the “Enter” button on your keyboard
The final formula will be as follows,
=SWITCH(C2,1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")You can apply the above formula to the rest of the cells by using the Fill Handle that appears at the lower-rightmost corner of the cell “C2” (where we have used the MONTH function).
How to Get a Month from Date using the TEXT function in Excel
The TEXT function in Excel converts a numeric value to a text string in a desired format.
The general syntax for the TEXT function is as follows,
=TEXT(value, format_text)Where,
- “value” – It represents the numeric value to be converted to text. It can be a number, date, or cell reference containing the same
- “format_text” – Here, you need to specify the desired output format. You must ensure that you use double quotations to avoid errors
In contrast to the method discussed above, the TEXT function is easy to use and quick. It doesn’t rely on any other function to obtain the month’s name.
Consider the following table, which includes employee details, including their full name and hiring date. Our task is to get the hiring months from the given dates.
Here are the steps,
- Open the desired spreadsheet
- Type “=TEXT”
- Select the first option from the popup or press “Tab” on your keyboard
- Now, select the cell reference value as “B2”
- Press “,” on your keyboard to move to the next argument
- Here, we require the full name of the month; for that, put “mmmm” in place of the “format” argument
(Make sure to use double quotation marks; otherwise, the TEXT function won’t work and will return an error) - End the formula with closing parenthesis “)”
- Press the “Enter” button on your keyboard
The final formula should look like below,
=TEXT(B2,"mmmm")To use this formula for the rest of the cells, hover to the lower-rightmost corner of cell C2 until you see an “+” icon. Then, click on that icon and drag the formula to the end of the table.
To Summarize: How To Extract Month From Date In Excel
Extracting the month from a date in Excel is straightforward. It can be done using the combination of the MONTH and SWITCH functions, as seen above.
You can also use TEXT functions to get the months’ names quickly.
Let me know if you need any help or are stuck somewhere while using the methods discussed above. I will try to answer all of your questions as soon as possible.
Also, explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.