A Complete Guide on How to Use the Excel Month Formula

By Indeed Editorial Team

Published May 8, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Microsoft Excel is a software that performs various functions. The MONTH formula allows you to sort, extract, and analyze data using the dates in an entry. Understanding how to apply the excel MONTH formula can help you pursue an analytics role. In this article, we discuss what the Excel MONTH formula is and outline the various ways to use it.

Related: Advanced Excel Skills: Definitions and Examples

What is the excel MONTH formula?

The Excel MONTH formula is one of its date or time functions. It helps discover and extract a month from a particular data in a spreadsheet. The MONTH function displays its results as an integer between 1 and 12, since there are 12 months in a year. You can also use this function to input a month into other functions like DATE. The MONTH function operates in the 2016-2020 versions of Excel. It's useful for financial analyses and calculations.

The MONTH function requires a single argument, which is the serial number representing a date. This can either be an actual serial number, a cell reference, or a manually entered date. When entering a date manually, it's best to follow the order of Excel's DATE function, which is the year, month, and day. For example, 2015-03-1. While you can input your date in other formats like date, month, year, you may experience complications when performing more complex functions. You can also pair the MONTH formula with other functions like the TODAY function.

Related: A List of Intermediate Skills in Excel to Include in Your Resume

How to use the MONTH function

Here's an overview of the seven ways you can use the MONTH function in Excel:

1. Extracting a month number from a date

There are various methods you can use to extract a month number from a date, and they include the following:

Using the MONTH function

This is the ideal method to extract the month numbers, as it returns the results as numerical strings. To do so, follow these steps:

  • Select the cell to display the extracted month number.

  • Enter the formula "=MONTH" in the cell or directly into the formula bar.

  • After typing the formula, click on the cell containing the date you want to extract and add a closing bracket.

  • Rather than clicking the cell, you can choose to manually type the date to extract and include your closing bracket. e.g. =MONTH(2014,5,23)

  • Press the Enter key to display the result of the function.

Using the TEXT function

The TEXT function returns the results as a text string, which is unsuitable if you intend to perform further calculations. To use this function, follow these steps:

  • Select the cell where you want the results to appear.

  • Enter the formula "=TEXT" in the cell or directly into the formula bar.

  • Select the cell containing the date you want to extract.

  • Include a comma and add "m" if you want the month number to appear without a zero in front of it and "mm" if you want a zero in front of it.

  • Press Enter to view your results.

2. Converting a month number to a month name in Excel

There are two ways you can convert a month number to a month name in Excel, and they include:

Using the TEXT function

  • Click on the cell where you want your results to appear.

  • Enter the formula "=TEXT" directly into the cell or in the formula bar.

  • Select the cell containing the month number to convert to a month name.

  • Include an asterisk followed by the number 28 because Excel considers 1 as the 1st of January, 1900 and multiplying by 28 allows you to move between months.

  • Then, include a comma and add "mmm" for an abbreviated month name like "Jan" or "mmmm" for a full month name like "January." e.g. =TEXT(A3*28, "mmm")

  • You can also choose to combine the TEXT function with the DATE function to achieve the same results. e.g =TEXT(DATE(2016,A3,1), "mmm")

Using a named range and VLOOKUP

  • To create a named range, select a cell and enter the number 1, then press the Enter key to go to the cell below and insert the number 2.

  • Continue the above step till you enter the number 12 in the twelfth cell, or you can select the first two cells and drag your cursor over the remaining cells for Excel to fill them in automatically.

  • Then to the right of the cells with numbers, enter your month names, with "January" beside "1," "February" beside "2," and so on till "December" is beside "12."

  • Next, select all the cells containing the numbers and the month name.

  • Select the Name Box, and create a named range.

  • Then, select the cell where you want the month name to appear.

  • Type "=VLOOKUP" into the cell and select the cell containing the serial number you want to convert into a month name, followed by a comma.

  • Enter your named range, followed by a comma, then enter the number of the column in the named range where you want Excel to search for a value, followed by a closing bracket.

  • Press Enter to view your results, or you can select the cell and drag your cursor to other cells to replicate the formula.

Related: 18 Data Analyst Skills for Success

3. Converting month name to number

Performing this function involves combining the MONTH and DATEVALUE functions. Here are some steps you can follow:

  • First, select the cell where you want the month number to appear.

  • Next, enter the formula "=MONTH (DATEVALUE(A2 & "1"))" into the cell or the formula bar.

  • The purpose of including "1" is, so the DATEVALUE function understands it's looking for a date.

  • Press Enter to view the results of the formula.

4. Obtaining the last day of the month in Excel

To find the last day of the month in a spreadsheet, you may use the EOMONTH function. You can implement this formula through the following steps:

  • First, select the cell where you want your results to appear.

  • Then, enter the formula "=EOMONTH" into the cell or the formula bar.

  • Enter the start date where you want Excel to count from by either referencing a cell containing it or inserting the date manually.

  • Include a comma and a number to represent the number of months before or after the start date that you want Excel to search.

  • Positive numbers are for future months, while negative numbers are for past months. If you want Excel to calculate the same month as the starting date, enter "0."

  • Next, close the bracket and press Enter to view your results.

5. Finding the first day of the month in Excel

To find the first day of the month, you can use these methods:

Extracting through the month number

  • Select the cell where you want your results to appear.

  • Enter the formula "=DATE" and include the year, month number and "1."

  • If you've already entered your month number in the spreadsheet, you can reference the cell.

  • Press the Enter key to display the results.

Extracting from a date

This method involves combining the MONTH and DATE functions. Here are the steps:

  • Select the cell where you want your results to appear.

  • Next, enter the formula "=DATE(year, MONTH(cell with date), 1)."

  • Press Enter to display the results of the formula.

Extracting using the current date

  • Select the cell where you want the results to appear.

  • Enter the formula "=EOMONTH(TODAY(),-1) +1" to get the first date of the current month.

  • You can amend the formula to return results for the following or previous months by adding or subtracting 1. For example "=EOMONTH(TODAY(), -2) +1" gives results for the previous month and so on.

  • Alternatively, you can enter the formula "=DATE(YEAR(TODAT() ), MONTH (TODAY () ), 1)" for the same results.

6. Calculating the number of days in a month

Here are the methods you can use to calculate the number of days in a month:

Extracting number of days using the month number

  • Select the cell where you want the results to appear.

  • Input the formula "=DAY(DATE(year, month number + 1, 1) -1)."

  • The formula automatically returns the number of days in the following month, so it's necessary to include "-1" to get results for the current month.

Extracting number of days using the date

  • Select the cell where you want the results to appear.

  • Input the formula "=DAY(DATE(YEAR(cell containing date), MONTH (cell containing date) +1, 1) -1)."

  • You can also enter the year and month manually instead of referencing a cell.

  • Alternatively, you can use this formula "=DAY(EOMONTH (cell containing date, 0)."

7. Summing up data using the month

You may want to sum up data by using the month as a selection criterion. Follow these steps to achieve it:

  • Assuming that your dates are in column A, and the values you want to add are in column B, create a helper column in C by entering the formula "=MONTH(cell with date)" to extract the month numbers.

  • Next, create an additional column D where you enter the serial numbers of the months you want to use in your calculation.

  • Finally, in column F, input the formula "=SUMIF($C$2:$C$15, $E2, $B$2:$B$15)" to sum up values in column B, using the month as a selection criterion.

Please note that none of the companies, institutions, or organizations mentioned in this article are affiliated with Indeed.

Explore more articles