How To Use The Month Formula In Excel

This article explains how to use the MONTH function in Excel to get a month number from a date and turn it into the month’s name. Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013.

What to Know

• The syntax is: MONTH(serial_number). The serial_number is the date from which you want to extract the month.Select the cell that will display the serial number, then go to the Formula bar, enter =month, and double-click MONTH.Select the date to extract the serial number for the month, enter a closing parenthesis, then press Enter.

The Syntax of the MONTH Function

The MONTH function in Excel returns a number between 1 and 12. This number corresponds to the month for the date in the selected cell or range.

The syntax of the MONTH function is: MONTH(serial_number)

The date must be properly entered with the DATE function in Excel.

The serial_number is the date from which you want to extract the month and must be a valid Excel date.

How to Use Excel to Get a Month From a Date

When your Excel worksheet contains a column of dates that are in a date format that Excel recognizes, use the MONTH function to extract the serial number for the month and place the serial number in a separate column.

• Select the cell that will display the serial number for the month.
• Go to the Formula bar and enter =month. As you type, Excel suggests a function.
• Double-click MONTH.
• Select the cell containing the date from which you want to extract the serial number for the month. For example, select the first cell in a column of dates.
• Enter a closing parenthesis, then press Enter.
• The result appears in the selected cell.
• To apply the formula to the other dates in the column, select the cell that contains the MONTH function, then drag the Fill Handle to the bottom of the column.
• The serial numbers for the dates display in the highlighted cells.

The Excel MONTH function extracts the month from a list of dates. The month displays as a serial number between 1 and 12. If you want to convert this number to text, create a named range.

Select the cell that will display the serial number for the month.

Go to the Formula bar and enter =month. As you type, Excel suggests a function.

Double-click MONTH.

Select the cell containing the date from which you want to extract the serial number for the month. For example, select the first cell in a column of dates.

Enter a closing parenthesis, then press Enter.

The result appears in the selected cell.

To apply the formula to the other dates in the column, select the cell that contains the MONTH function, then drag the Fill Handle to the bottom of the column.

The serial numbers for the dates display in the highlighted cells.

How to Convert a Month Number to Text

There’s a two-step process to convert the serial number for a month to the text name. First, create a named range, then use the named range to convert the serial number to text.

Create a Named Range

The first step to convert a serial number to a month’s name is to create a range. This range contains the number and the corresponding month.

• Select a cell, enter a 1, then press Enter to go to the cell below.
• Enter a 2.
• Select both cells.
• Drag the fill handle until the number 12 displays next to the Fill handle.
• Select the cell to the right of the number 1 and enter January. Or, enter the format you want for the month. For example, use Jan for January.
• Drag the fill handle down until the word December displays next to the Fill Handle.
• Select the serial number and month name cells.
• Go to the Name Box and enter a name for the range.
• Press Enter to create the named range.

Convert the Number to Text

The next step is to select the column where you want to insert the text version of the month.

The data for the named range can be on the same worksheet or on another worksheet in the workbook.

Select a cell, enter a 1, then press Enter to go to the cell below.

Enter a 2.

Select both cells.

Drag the fill handle until the number 12 displays next to the Fill handle.

Select the cell to the right of the number 1 and enter January. Or, enter the format you want for the month. For example, use Jan for January.

Drag the fill handle down until the word December displays next to the Fill Handle.

Select the serial number and month name cells.

Go to the Name Box and enter a name for the range.

Press Enter to create the named range.

• Select the cell next to the first serial number in the column.
• Enter =vlookup. As you type, Excel suggests possible functions. Double-click VLOOKUP.
• Select the first serial number in the column, then enter a comma.
• Enter the named range, then enter a comma.
• Enter the number of the column in the named range that you want to display, enter a closing parenthesis, then press Enter.
• Select the month and drag the Fill Handle to the bottom of the column.
• The month names appear in the column.

Select the cell next to the first serial number in the column.

Enter =vlookup. As you type, Excel suggests possible functions. Double-click VLOOKUP.

Select the first serial number in the column, then enter a comma.

Enter the named range, then enter a comma.

Enter the number of the column in the named range that you want to display, enter a closing parenthesis, then press Enter.

Select the month and drag the Fill Handle to the bottom of the column.

The month names appear in the column.

Get the Latest Tech News Delivered Every Day