Exceljet

Quick, clean, and to the point

Excel MONTH Function

Excel MONTH function
Summary 

The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function.

Purpose 
Get month as a number (1-12) from a date
Return value 
A number between 1 and 12.
Syntax 
=MONTH (serial_number)
Arguments 
  • serial_number - A valid Excel date.
Version 
Usage notes 

The MONTH function extracts the month from a given date as a number between 1 to 12. For example, given the date "June 12, 2021", the MONTH function will return 6 for June. MONTH takes just one argumentserial_number, which must be a valid Excel date.

Dates can be supplied to the MONTH function as text (e.g. "13-Aug-2021") or as native Excel dates, which are large serial numbers. To create a date value from scratch with separate year, month, and day inputs, use the DATE function.

The MONTH function will "reset" every 12 months (like a calendar). To work with month durations larger than 12, use a formula to calculate months between dates.

The MONTH function returns a number. If you need the month name, see this example.

Examples

To use the MONTH function, supply a date:

=MONTH("23-Aug-2012") // returns 8
=MONTH("11-May-2019") // returns 5

With the date "September 15, 2017" in cell A1, MONTH returns 9:

=MONTH(A1) // returns 9

You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function. The formula below extracts the month from the date in cell A1 and uses the TODAY and DATE functions to create a date on the first day of the same month in the current year.

=DATE(YEAR(TODAY(),MONTH(A1),1) // same month current year

See below for more examples of formulas that use the MONTH function.

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply the number format of your choice.

Notes

  • MONTH will return #VALUE! if a date is not recognized.
  • MONTH will return #NUM! if a date is supplied as a number that is out of range (i.e. -1).

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.