How to Use Excel MONTH Function (6 Examples)

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.

Introduction to the Excel MONTH Function

• Summary

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

• Syntax

=MONTH(serial_number)

• Arguments

• Return Value

A number between 1 to 12.

How to Use Excel MONTH Function: 6 Examples

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, that you have to provide dates in a recognizable format while using the MONTH function.

Steps:

• First, type the below formula in Cell C5.

• 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.

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 of 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.

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.

• 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.

• 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.

• 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’.

Download the Practice Workbook

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

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.