What is the Excel MONTH Function?
The Excel MONTH function extracts the month number from a date, returns an integer number, ranging from 1 (January) to 12 (December).
Use the DAY function to extract a day number and use the YEAR function to extract a year number
MONTH Syntax
MONTH(serial_number)
serial_number, required, a valid date in a format Excel recognizes.
Usage Note:
- Excel returns a #VALUE! error if the date in the serial_number argument outside the range January 1, 1900, to December 31, 9999.
How to Use MONTH Function in Excel
Look at the picture below; there are seven dates in column A with various display formats. Column B contains the MONTH function for each date in the same row.
Column B displays the numbers 1 through 12 depending on the month data for each date, but row 7 and 8 generate a #VALUE! errors. Why? Let’s see one by one.
MONTH Function #1 – Month Function #5
The date display on the worksheet can have various formats, depending on what kind of display we will format, but a valid Excel date has the same format according to the Windows regional setting for each computer.
If you want to know a valid excel date format, look at the formula bar. The image below shows the windows regional settings have the mm/dd/yyyy format, a valid excel date format will make the MONTH function return numbers between 1 and 12.
MONTH Function #6
The date in cell A7 has the same display format as the date of the cell A2, but why does the MONTH function return the correct month number for cell A2 and return an error for cell A7?
Please see the picture above, cell A7 display is the same as cell A2, but data in cell A7 is a text. Look at the formula bar, the valid excel date format is different, there is a single quotation mark in front, this indicates that data in cell A7 is a text, not a date.
MONTH Function #7
Why does the MONTH function #7 return errors? Isn’t the valid excel date format the same as windows regional settings. Right, but the date entered outside January 1, 1900, to December 31, 9999 range. Outside the range is the cause of the error.
