MONTH Excel Function

Month Function in excel is a date function which is used to find out the month for a given date in a date format, this function takes an argument in a date format and the result displayed to us is in integer format, the value this function gives us is in the range of 1-12 as there are only twelve months in an year and the method to use this function is as follows =Month( Serial Number), the argument provided to this function should be in a recognizable date format of excel.

MONTH Function in Excel

MONTH function in Excel gives the month from its date. It returns the month number ranging from 1 to 12.

MONTH Formula in Excel

Below is the MONTH Formula in Excel.

MONTH Formula

Or

MONTH( date )

MONTH in Excel – Illustration

Suppose a date (10 August, 18) is given in cell B3, and you want to find the month in Excel of the given date in numbers.

MONTH Illustration -1

You can simply use the MONTH Formula in Excel given below:

= MONTH (B3)

MONTH Illustration - 2

and press Enter. MONTH function in Excel will return 8.

MONTH Illustration - 3

You can also use the following MONTH Formula in Excel:

= MONTH (“10 Aug 2018”)

MONTH Illustration - 4

and press Enter. MONTH Function will also return the same value.

MONTH Illustration -5

The date 10 Aug 2018 refers to a value 43322 in Excel. You can also use this value directly as input to the MONTH Function. The MONTH Formula in Excel would be:

= MONTH (43322)

MONTH Illustration -6

MONTH function in Excel will return 8.

MONTH Illustration - 7

Alternatively, you can also use the date in another format as:

= MONTH (“10-Aug-2018”)

MONTH Illustration -8

Excel MONTH Function will also return 8.

MONTH Illustration - 9

Now, let us look at some examples of where and how to use the MONTH function in Excel in various scenarios. 

How to Use MONTH Function in Excel?

MONTH in Excel is very simple and easy to use. Let us understand the working of a MONTH in excel by some examples.

serial_number: a valid date for which the month number is to be identified

The input date must be a valid Excel date. The dates in Excel are stored as serial numbers. For example, the date Jan 1, 2010, is equal to the serial number 40179 in Excel. MONTH Formula in Excel takes as input both the date directly or the serial number of the date. It is to be noted here that Excel does not recognize dates earlier than 1/1/1900.

Returns

MONTH in Excel always returns a number ranging from 1 to 12. This number corresponds to the month of the input date.

You can download this MONTH Function Excel Template here – MONTH Function Excel Template

MONTH in Excel Example #1

Suppose you have a list of dates given in the cells B3: B7, as shown below. You want to find the month name of each of these given dates.

MONTH in Excel Example #1

You can do so using the following MONTH Formula in Excel:

= CHOOSE ((MONTH(B3)), “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)

MONTH Example 1-1

MONTH (B3) will return 1.

CHOOSE (1, …..) will choose the 1st option of the given 12, which is Jan here.

So, a MONTH in Excel will return Jan.

MONTH Example 1-2

Similarly, you can drag it for the rest of the cells.

MONTH Example 1-3

Alternatively, you can use the following MONTH Formula in Excel:

= TEXT (B3, “mmm”)

MONTH Example 1-4

MONTH Function will return Jan.

MONTH Example 1-5

MONTH in Excel Example #2

Suppose you have month names (say in “mmm” format) given in cells B4: B15.

MONTH Example 2

Now, you want to convert these names to the month in numbers.

You can use the following MONTH in Excel:

= MONTH ( DATEVALUE( B4 & ” 1”) 

MONTH Example 2-1

MONTH Example 2-2

For Jan, the MONTH in Excel will return 1. For Feb, it will return 2 and so on.

MONTH Example 2-3

MONTH in Excel Example #3

Suppose you have a list of holidays given in cells B3: B9 in a date-wise manner, as shown below.

MONTH Example 3

Now, you want to calculate the number of holidays each month. To do so, you can use the following MONTH Formula in Excel for the first month given in E4:

= SUMPRODUCT( –( MONTH( $B$4:$B$16 ) = MONTH( DATEVALUE( E4 & ” 1″)) ) )

MONTH Example 3-1

and then drag it to the rest of the cells.

Let us look at MONTH in excel detail:

  • MONTH( $B$4:$B$16 ) will check the month of the dates provided in cell range B4:B16 in a number format. MONTH function in Excel will return {1; 1; 2; 3; 4; 5; 6; 6; 8; 9; 10; 11; 12}
  • MONTH( DATEVALUE( E4 & ” 1″) will give the month in number corresponding to cell E4 (see example 2). MONTH function in Excel will return 1 for January.
  • SUMPRODUCT in Excel (– (…) = (..) ) will match the month given in B4:B16 with January (=1) and will add one each time when it is true.

Since January appears twice in the given data, the MONTH function in Excel will return 2.

MONTH Example 3-2

Similarly, you can do for the rest of the cells.

MONTH Example 3-3

MONTH in Excel Example #4

Suppose you have sales data for the past two years. The data was collected on the last date of the month. The data was manually entered, so there could be a mismatch in the data. You are supposed to compare the sales between 2016 and 2017 for each month.

MONTH Example 4

To check if the months are the same and then compare the sales, you can use the MONTH Formula in Excel:

=IF( (MONTH(B4)) = (MONTH(D4) ), IF( E4 > C4, “Increase”, “Decrease” ),  “Month-Mismatch” )

MONTH Example 4-1

for the 1st entry. MONTH function in Excel will return “Increase.”

MONTH Example 4-2

Let us look at the MONTH in Excel in detail:

If the MONTH of B4 (i.e., for 2016) is equal to the MONTH given in D4 (for 2017),

  • MONTH function in Excel will check if the sales for the given month in 2017 is greater than the sales of that month in 2016.
    • If it is greater, it will return “Increase.”
    • Else, it will return to “Decrease.”

If the MONTH of B4 (i.e., for 2016) is not equal to the MONTH given in D4 (for 2017),

  • MONTH function in Excel will return “Mis-match.”

Similarly, you can do for the rest of the cells.

MONTH Example 4-3

You could also add another condition to check if the sales are equal and return “Constant.”

MONTH in Excel Example #5

Suppose you work in the Sales department of your company, and you have a date-wise data of how many products were sold on a particular date for the previous year, as shown below.

MONTH Example 5

Now, you want to club the number of products in a month-wise manner. To do so, you use the following MONTH Formula in Excel:

= SUMPRODUCT(– ( EXACT( F4, MONTH( $B$4:$B$17 ))), $C$4:$C$17 )

MONTH Example 5-1

for the first cell. MONTH function in Excel will return 16.

MONTH Example 5-2

and then drag it to the rest of the cells.

MONTH Example 5-3

Let us look at the MONTH in Excel in detail:

= SUMPRODUCT(– ( EXACT( F4, MONTH( $B$4:$B$17 ))), $C$4:$C$17 )

  • MONTH( $B$4:$B$17 ) will give the month of the cells in B4: B17. MONTH function in Excel will return a matrix {2; 8; 3; 2; 1; 7; 2; 5; 9; 6; 12; 11; 4; 10}
  • EXACT( F4, MONTH( $B$4:$B$17 )) will match the month in F4 (i.e., 1 here) with the matrix and will return another matrix with TRUE when it’s a match or FALSE otherwise. For the 1st month, it will return {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
  • SUMPRODUCT (– (..), $C$4:$C$17) will sum the values given in C4:C17 when the corresponding value in the matrix is TRUE.

MONTH function in Excel will return 16 for January.

MONTH Example 5-4

Things to remember about a MONTH in Excel

  • The MONTH function returns the month of the given date or serial number.
  • Excel MONTH Function is given #VALUE! Error when it cannot recognize the date.
  • The Excel MONTH Function accepts dates only after 1 Jan 1900. It will give the #VALUE! Error when the input date is earlier than 1 Jan 1900.
  • The MONTH Function in excel returns the month in number format only. Therefore, its output is always a number between 1 and 12.

MONTH Excel Function Video

Recommended Articles

This has been a guide to MONTH Function in Excel. Here we discuss the MONTH Formula in excel and how to use the MONTH function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *