This article explains the syntax and the use of Excel MONTH function in Excel.
The Excel MONTH function returns the serial number corresponding to a month of a date ranging from 1 (January) to 12 (December). In other words, MONTH function extracts the month value from a date in a cell.
The MONTH function returns the month component from a date, but the use of MONTH function is rarely used alone. Usually, the function used in conjunction with other Excel functions and formulas, like the DATE function.
- serial_number: A valid date of the month you want to find. It can be a reference to a cell, a date returned by using the DATE function, result of other Excel formulas/functions or supplied directly within double quotes in the same format as system displays.
Tip: Rather supplying date directly in MONTH function, it is more advisable to refer a cell containing date or date returned by some other formula. For example, =MONTH (DATE (2018, 4, 1) ) returns 4th month since DATE returns April 1, 2018.
Examples: Excel MONTH Function
The following example will make you familiar with Excel MONTH function and its uses in real life.
The function in cell A2, =MONTH (A2) returns the month ‘2’ of the date (6-Feb’18).
In the example above, despite having dates in the different format, MONTH function returns the month value from dates because Excel ignores custom date formatting while calculation and only consider the actual value of a cell.
The serial number in cell A5 (43167) is the serial number of the date March 8, 2018, hence it returns 4 as the 4th month.
Find the Current Month Value
Let’s assume you want to find which month is running. The following formula in any cell of the spreadsheet helps in extract the current month value.
=MONTH (TODAY() )
In the example above, TODAY() returns the serial number of current date, i.e. April 30, 2018, which instructs MONTH to extract the month that is 4 in this case; hence, MONTH function returns 4.
Additional Notes: –
- The MONTH function occurs #VALUE! error if invalid date supplied as serial_number.
- Excel accepts valid dates from January 1st, 1900, to December 31st, 9999.
- The MONTH function returns ‘1’ if the referred cell or a result of the formula returns to 0 or empty cell.
- #NUM! error occurs if negative value supplied in MONTH function.
- Use ‘Custom Date Formatting’ if MONTH function returns the result in date formatting (e.g. “01/01/1900”) instead of returning an integer.