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: With the date “September 15, 2017” in cell A1, MONTH 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. 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).
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
