Excel **MONTH** function is an effective function to extract the month from a given date. This function returns the month as a number between **1** to** 12**. The **MONTH** function is a **DATE **function in excel. In this article, we will discuss the details of this function. Besides that, we will show some examples regarding the application of the **MONTH **function.

**Table of Contents**hide

**Download the Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**Introduction to the Excel MONTH Function**

**Summary**

Get the month, as a number from** 1** (**January**) to** 12** (**December**).

**Syntax**

**=MONTH(serial_number)**

**Arguments**

Argument | Requirement | Explanations |
---|---|---|

serial_number |
Required | A recognizable date. For example, 23-Jul-21 |

**Return Value**

A number between **1** to **12**.

**6 Examples to Use Excel MONTH Function**

**1. Simply Use Excel MONTH Function to Get the Month Number**

The simplest application of the **MONTH** function is to provide a valid date format as an argument. Let’s consider a dataset containing different valid date formats. One important thing is, you have to provide dates in a recognizable format while using** the MONTH** function.

**Steps:**

- First, type the below formula in
**Cell C5**.

`=MONTH(B5)`

- Consequently, the month number regarding the dates will be shown as output. Use
**Fill Handle**(**+**) to copy the formula to the rest of the cells.

**Note:**

➤ If you do not provide an invalid date format, the **MONTH** function will return an error. For instance, ‘**10/22/2021**’ is an invalid date. Let’s see the outcome of applying the** MONTH** function to it.

➤ So, here is the error (**#VALUE!**) returned by the **MONTH** function.

**2. Combination of MONTH & DATE Functions in Excel**

Sometimes, we need to find the month number even if the given date format is improper. In such cases, we can combine** the** **DATE function** with the **MONTH** function. However, in this process, you have to type the date manually in the formula. As an example, we will find the month number for the date ‘**2021 April 17th**’.

**Steps:**

- Type the following formula in
**Cell C5**.

`=MONTH(DATE(2021,4,23))`

Here, the **DATE** function converts the improper date value to a recognizable format. Later, the **MONTH** function extracts the month number from the date.

- Subsequently, you will get the following result.

- Similarly, you can find months of other improper dates too.

**3. Apply MONTH and DATEVALUE Functions Converting Month Value to Number**

Suppose, we have a list of month’s names both in ‘**mmm**’ and ‘**full**’ formats and we want to find out the number regarding these months. In such cases, we can use **the DATEVALUE function **along with the** MONTH** function.

**Steps:**

- Initially, type the below formula in
**Cell C5**.

`=MONTH(DATEVALUE(B5&"1"))`

Here, the **DATEVALUE **function converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code. So, here **DATEVALUE(B5&”1”) **returns ‘**36892**’. And finally, the **MONTH** function extracts the month value from ‘**36892**’.

- As a consequence, you will get the following result.

**4. Excel MONTH and YEAR Functions Combination**

Using the** MONTH** function, you can compare months and years between two dates. However, for this task, you have to use** the YEAR function **along with the **MONTH** function. Now, we will compare the dates in range** B5:D7**.

**Steps:**

- First, type the below formula in
**Cell D5**.

`=MONTH(B5)&YEAR(B5)=MONTH(C5)&YEAR(C5)`

- As a result, the following will be the outcome.

🔎 **How Does the Formula Work?**

**➨**** MONTH(B5)**

Here, the **MONTH** function extracts the month number ‘**12**’.

**➨**** YEAR(B5)**

Here, the **YEAR** function returns the year of ‘**01-Dec-21**’, which is ‘**2021**’.

**➨**** MONTH(B5)&YEAR(B5)=MONTH(C5)&YEAR(C5)**

Finally, this formula matches both the dates and returns ‘**TRUE**’.

**5. Use MONTH Function with SUMPRODUCT Function**

We can find the number of days month-wise from a list of dates. For example, we have a list of dates (holidays) and we want to find how many holidays each month has. So, to do this task we will use the **MONTH** function with **the SUMPRODUCT function**.

**Steps:**

- First, type the below formula in
**Cell D5**.

`=SUMPRODUCT(--(MONTH($B$5:$B$14)=MONTH(DATEVALUE(C5&"1"))))`

- In the end, you will get the holidays month-wise.

🔎 **How Does the Formula Work?**

**➨**** MONTH($B$5:$B$14)**

Here, this part of the formula will return the month number of the range **B5:D14**.

{1,2,1,3,4,5,3,3,6,7}

**➨**** MONTH(DATEVALUE(C5&”1″))**

Now, this part will return the number value of the month of** Cell E4**.

**➨**** SUMPRODUCT(–(MONTH($B$5:$B$14)=MONTH(DATEVALUE(C5&”1″))))**

Finally, this formula will match the month given from **MONTH(B5:B14)** with **the result of MONTH(DATEVALUE(C5&”1″))** and add **1** when the result is matched.

**6. Apply MONTH and CHOOSE Functions Combination in Excel**

Let’s assume we have a list of dates and we want to find out the names of the regarding dates. We can use the Excel **MONTH** function along with **the CHOOSE function**.

**Steps:**

- Type the below formula in
**Cell C5**.

`=CHOOSE((MONTH(B5)),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")`

- Finally, you will get the month name (in ‘
**mmm**’ format). Use**Full Handle**(**+**) to copy the formula to the rest of the cells.

🔎 **How Does the Formula Work?**

**➨**** MONTH(B5)**

Here, this part of the formula will return ‘**12**’.

**➨**** CHOOSE((MONTH(B5)),”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”)**

Now, the **CHOOSE** function chooses a value or action to perform from a list of values, based on an index number. So, this formula will choose ‘**Dec**’ from the list as the result of the previous part of the formula is ‘**12**’.

**Conclusion**

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.