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.


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)

Introduction to the Excel MONTH Function

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

Simply Use Excel MONTH Function to Get the Month Number

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

Simply Use Excel MONTH Function to Get the Month Number

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.

Simply Use Excel MONTH Function to Get the Month Number

➤ 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))

Combination of MONTH & DATE Functions in Excel

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.

Combination of MONTH & DATE Functions in Excel

  • 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"))

Apply MONTH and DATEVALUE Functions Converting Month Value to Number 

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)

Excel MONTH and YEAR Functions Combination 

  • 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"))))

Use MONTH Function with SUMPRODUCT Function

  • 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")

Apply MONTH and CHOOSE Functions Combination in Excel

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

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo