Excel MONTH Function

Use the MONTH function to extract the month from a specified date in Excel. See multiple examples and get step-by-step instructions below.

01
of 03

Extract the Month from a Date with the MONTH Function

Extract the Month from a Date with the Excel MONTH Function
Extract the Month from a Date with the Excel MONTH Function. © Ted French

The MONTH function can be used to extract and display the month portion of a date that has been entered into the function.

One common use for the function is to subtract dates in Excel that occur in the same year as shown in row 8 of the example in the image above.

02
of 03

​MONTH Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the MONTH function is:

= MONTH ( Serial_number )

Serial_number - (required) a number representing the date from which the month is extracted.

This number can be:

  • serial number representing a date - row 6 in the example;
  • cell reference to the worksheet location of a date - row 4 in the example;
  • a date entered as the function's argument using the DATE function - row 6 in the example;
  • the current date entered as the function's argument using the TODAY or NOW functions - row 7 in the example.

Serial Numbers

Excel stores dates as sequential numbers - or serial numbers - so they can be used in calculations. Each day the number increases by one. Partial days are entered as fractions of a day - such as 0.25 for one quarter of a day (six hours) and 0.5 for half a day (12 hours).

For Windows versions of Excel, by default:

  •     January 1, 1900 = serial number 1;
  •     January 1, 2016 = 42370 (since it is 42,370 days after January 1, 1900);
  •     12 o'clock noon on January 1, 2016 is 42370.50.

Naming a Month Example

The examples in the image above displays a variety of uses for the MONTH function, including combining it with the CHOOSE function in a formula to return the name of the month from the date located in cell A1.

How the formula works is:

  1. The MONTH function extract the number of the month from the date in cell A1;
  2. The CHOOSE function returns the month name from the list of names entered as the Value argument for that function.

As shown in cell B9, the final formula looks like this:

=CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","June","July","Aug","Sept","Oct","Nov","Dec")

Below are listed the steps used to enter the formula into the worksheet cell.

03
of 03

Entering the CHOOSE/MONTH Function

Options for entering the function and its arguments include:

  1. Typing the complete function shown above into a worksheet cell;
  2. Selecting the function and its arguments using the CHOOSE function dialog box

Although it is possible to just type the complete function in manually, many people find it easier to use the dialog box which looks after entering the correct syntax for the function - such as the quotation marks surrounding each month name and the comma separators between them.

Since the MONTH function is nested inside CHOOSE, the CHOOSE function dialog box is used and MONTH is entered as the Index_num argument.

This example returns the short form name for each month. To have the formula return the full month name - such as January rather than Jan or February instead of Feb, enter the full month name for the Value arguments in the steps below.

The steps for entering the formula are:

  1. Click on the cell where the formula results will be displayed - such as cell A9;
  2. Click on the Formulas tab of the ribbon menu;
  3. Choose Lookup and Reference from the ribbon to open the function drop down list;
  4. Click on CHOOSE in the list to bring up the function's dialog box;
  5. In the dialog box, click on the Index_num line
  6. Type MONTH(A1) on this line of the dialog box;
  7. Click on the Value1 line in the dialog box;
  8. Type Jan on this line for January;
  9. Click on the Value2 line;
  10. Type Feb;
  11. Continue entering the names for each month on separate lines in the dialog box;
  12. When all month names have been entered, click OK to complete the function and close the dialog box;
  13. The name May should appear in the worksheet cell where the formula is located since May is the month entered into cell A1 (5/4/2016);
  14. If you click on cell A9, the complete function appears in the formula bar above the worksheet.
Was this page helpful?