How to Use Excel MONTH Function (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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)

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.


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

Read More: How to Convert Month to Number in Excel


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

Read More: Excel Formula for Current Month and Year


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

Read More: Convert 3 Letter Month to Number in Excel


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.


Excel MONTH Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo