MONTH Function

It returns the month part as a number from a given date.

Author: Osman Ahmed
Osman Ahmed
Osman Ahmed
Investment Banking | Private Equity

Osman started his career as an investment banking analyst at Thomas Weisel Partners where he spent just over two years before moving into a growth equity investing role at Scale Venture Partners, focused on technology. He's currently a VP at KCK Group, the private equity arm of a middle eastern family office. Osman has a generalist industry focus on lower middle market growth equity and buyout transactions.

Osman holds a Bachelor of Science in Computer Science from the University of Southern California and a Master of Business Administration with concentrations in Finance, Entrepreneurship, and Economics from the University of Chicago Booth School of Business.

Reviewed By: Elliot Meade
Elliot Meade
Elliot Meade
Private Equity | Investment Banking

Elliot currently works as a Private Equity Associate at Greenridge Investment Partners, a middle market fund based in Austin, TX. He was previously an Analyst in Piper Jaffray's Leveraged Finance group, working across all industry verticals on LBOs, acquisition financings, refinancings, and recapitalizations. Prior to Piper Jaffray, he spent 2 years at Citi in the Leveraged Finance Credit Portfolio group focused on origination and ongoing credit monitoring of outstanding loans and was also a member of the Columbia recruiting committee for the Investment Banking Division for incoming summer and full-time analysts.

Elliot has a Bachelor of Arts in Business Management from Columbia University.

Last Updated:November 10, 2023

What is the MONTH Function?

The MONTH function in Excel returns the month part as a number from a given date. The value returned can range from 1 to 12, corresponding from January to December, depending on the data supplied to the function. 

Excel stores data in serial numbers from 1st January 1900, corresponding to serial number 1. Thus, 2nd January 1900 would correspond to serial number 2.

Even though the dates are stored as serial numbers, Excel allows us to extract the day, month, and year components of the dates using the functions present in the library.

As a financial analyst, extracting the month from a date has many practical applications, such as grouping the bonds that pay off coupon payments in the same month, preparing rent rolls, etc. 

This article will guide you in understanding how to use the MONTH function and the scenarios where you will likely use it.

Key Takeaways

  • You will get a #VALUE! Error if the date is not in a recognizable format.
  • If you input negative numbers for the date, then Excel will return #NUM! Error.
  • The serial_number argument for the function can be a cell reference to a date, a formula that returns a date, or a date formatted as a serial number.
  • The MONTH function will only accept dates on or after 1st January 1900. Any date before that will return the #VALUE! Error. 

How the MONTH function works

The MONTH is categorized as a Date and Time function that returns a number between 1 to 12, representing the twelve months from January to December in a particular year.

When you reference or hardcode a date in the MONTH function, say 8-May-2022, we will get the result as five or the fifth month, which equals May. 

Similarly, if the enclosed date is 12/4/2022 and the function returns the number 12, we have extracted the December month.

Dates

The syntax for the function

The syntax for the function is:

=MONTH(serial_number)

where,

  • serial_number = (required) a serial number, the cell containing the date or a formula that returns the date which will be used to extract the month 

Please note, As you know, dates can also be represented as serial numbers. For example, the dates in Excel begin from 1st January 1900 and hence occupy the serial number position as 1. 

Similarly, if you have the date as 4th December 2022, then the corresponding serial number will be 44899.

Examples of MONTH Function

We have illustrated several forms you will often see in Excel to understand how the function works with different date formats. 

examples

The date is the same, i.e., 2nd December 2022, but it can be represented in several formats from our beloved format cell dialog box.

The formula we will use to extract the month from the date is =MONTH(B3) which will give you the same result in all the cells in column D.

formula

This proves that even if you write the date in any of the readable formats in Excel, the function will efficiently work to return the month component from the date.

WSO Tip

Getting errors for wrong date formats inserted in Excel can be pretty standard. If you are a person who makes a lot of mistakes in date formats, say input date as DD-MM-YYYY instead of MM-DD-YYYY, then we recommend you use the DATE function. 

The function takes in three arguments - year, month, and date in the same order. So, if you need to input the date as 14th December 2022, then the formula will be =DATE(2022,12,14) which should give you the expected result.

a. Practical Example #1

Assume that you have a portfolio of several bonds. The bonds pay a semi-annual coupon payment, for which you receive an Excel file for any bonds that pay coupons in the next three months. 

To group all the bonds and find the total amount receivable from the coupon payments, we can use the combination of the MONTH and SUMIF functions.

Assume that the dataset looks as illustrated below:

example

Here, you can directly use the function or the combination of CHOOSE and MONTH functions(explained in the example ahead). We will go with the former option by inserting an additional column for the formula.

The formula will be =MONTH(C3) in column E, which will give you the result:

choose and month

Finally, we will use the SUMIF function such that the formula =SUMIF(E3:E12,12, D3:D12), which will give us the total coupon payments receivable for December as $868.00.

sumif

MONTH function vs. EOMONTH function

The most significant difference between both functions lies in their name. A MONTH generally returns the month from the date while the EOMONTH or you can also call it 'End of Month,' returns the last day of the month. 

The former function has a primary interest in the month component, while the latter has an interest in the date component.

The syntax for the EOMONTH function is:

=EOMONTH(start_date,months)

where,

  • start_date = starting date in Excel from which you need to find the last day
  • months = The number of months to go forward or backward in time 

For example, suppose that you have the dates in Excel as illustrated below:

month vs eomonth

By using the formula =MONTH(B3) in column C, the extracted months for the dates are as illustrated below:

formula

However, on the other hand, by using the formula =EOMONTH(B3,0) in column D, we first get the date serial numbers. 

formula

By changing the format to 'Short Date,' the final result would be as illustrated below:

shortdate

As you can see, the EOMONTH function returns the last date of the month from the start_date, which is different from what the MONTH function does. 

It proves that EOMONTH primarily focuses on the day component of the date while the MONTH function focuses on the month component.

Alternative methods to return month

The ability of Excel to perform a similar task but in a different way is quite astonishing. That way, the Excel user can take a different approach in different scenarios. 

Suppose you want to extract the month from a date but don't want to break up the data integrity(add or remove additional columns). How would you proceed? 

We have listed various alternatives you can use in similar scenarios while working in Excel.

Method #1 - Using the Format Cells dialog box

If you cannot insert additional columns in Excel but need to group the dates into months, we advise using the custom formatting from Format Cells.

  • Select the dates from the spreadsheet
  • Press the keyboard shortcuts of Ctrl + 1, which should open the window as illustrated below:

  • Click on Custom and type in the custom format as 'mmmm.'

  • Once you click on OK, the dates in the spreadsheet should look as illustrated below:

dates

Method #2 - CHOOSE function

The CHOOSE function returns a value from the specified list based on the index number that we input in the formula. For example, assume that you have three fruits on the list: 

  • Apple
  • Mango
  • Pineapple

If you input the index number as 2, the return fruit would be mango.

The MONTH function only returns the number corresponding to a particular month. However, using the CHOOSE function, you can directly replace the month's name in Excel.

choose function

In the example above, if you use the formula:  

=CHOOSE(MONTH(B3),"January","February","March","April","May","June","July","August","September","October","November","December")

It will give you the results:

example

There is just one thing you need to ensure - insert the months in a sequence inside the quotation marks and not randomly, or else the function won't work correctly.

Method #3 - TEXT function

The TEXT function in Excel allows the user to convert numbers into different formats and return the final result as a text string. Since the dates are also numbers, the text function will enable us to extract the months using formatting similar to custom formats.

Remember that we had used 'mmmm' as the custom format to return the months from the dates using the format cell option?

text function

Here, we will use the formula =TEXT(B3, "mmmm") where the format is enclosed within the quotation marks to return the result as:

text

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources