The Excel MONTH function returns the month of a date serial number between 1 and 12, representing January through December.
| Syntax: | =MONTH(serial_number) |
Where the serial_number is a date in a format Excel recognises known as the date-time serial number.
Excel MONTH Function Examples
Tip: Rows 11:15 contain dates as text in column B, but MONTH is still able to return the month number! We can exploit this feature to convert text months to month numbers which I’ll show you later.
You can also nest the DATE Function inside MONTH, like so:
Formula: =MONTH( DATE(2017,03,15) )
Result: =3
Or DATEVALUE like so:
Formula: =MONTH( DATEVALUE("2017/03/15") )
Result: =3
You can also use the MONTH function to populate the month argument of the DATE function e.g.
=DATE(2017,MONTH(B9),1)
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel, including all Date functions explained with examples - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the sample workbook.
Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.
Related Tutorials
| Convert months to fiscal quarters using the SWITCH Function | The Excel SWITCH Function (new in Excel 2016) is an alternative to nested IFs or VLOOKUP, by allowing you to replace, or switch one value for another. |
| Convert dates to calendar or fiscal quarters and years | Handy if your company financial year doesn't follow the calendar year. |
| Excel DATE Function | Returns a date in serial number from separate year, month and day values |
| Excel DATEVALUE Function | Converts a date text string to a date serial number. |
| Excel End of Period Dates | Calculate fiscal End of Period dates. i.e. dates that don't follow the calendar periods. |