How to Use the MONTH Formula in Excel

Extract the month from a list of dates

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

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 date must be properly entered with the DATE function in Excel.

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

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.

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

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

  3. Double-click MONTH.

    Dataset to extract the serial month number from a column of dates
  4. 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.

  5. Enter a closing parenthesis, then press Enter.

    Entering the MONTH function in Excel
  6. The result appears in the selected cell.

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

    Copying the MONTH function to a column of cells in Excel
  8. The serial numbers for the dates display in the highlighted cells.

    The results of filling the MONTH function in Excel

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.

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

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

  2. Enter a 2.

  3. Select both cells.

    Adding serial month numbers to an Excel worksheet to create a named range
  4. Drag the fill handle until the number 12 displays next to the Fill handle.

    Filling cells with the serial month numbers in an Excel worksheet
  5. 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.

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

    Creating the month entries for a named range in Excel
  7. Select the serial number and month name cells.

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

  9. Press Enter to create the named range.

    Selecting cells for a named range in Excel

Convert the Number to Text

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

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

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

    Entering the VLOOKUP function in Excel to convert serial numbers to month names
  3. Select the first serial number in the column, then enter a comma.

    Selecting the cell for the VLOOKUP formula
  4. Enter the named range, then enter a comma.

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

    Completing the VLOOKUP formula to convert a serial number to text in Excel
  6. Select the month and drag the Fill Handle to the bottom of the column.

    Filling the results of the VLOOKUP formula to a column
  7. The month names appear in the column.

    The results of the VLOOKUP formula with the converted serial numbers