Get month as a number (1-12) from a date
=MONTH(date)| Parameter | Description |
|---|---|
date |
A valid Excel date. |
MONTH takes just one argument, which must be a valid date or a text value that Excel can convert into a date. With the date March 15, 2026 in cell A1,
=MONTH(A1) // returns 3
=MONTH("15-Mar-2026") // returns 3
=MONTH(DATE(2026,3,15)) // returns 3In the worksheet below, the goal is to extract the month number from dates. The formula in C5 is:
=MONTH(B5)One thing that confuses new users to Excel is how to get the month name with the MONTH function. Short answer: you can't. The MONTH function returns a
=TEXT(B5,"mmmm")The MONTH function can be combined with the ROUNDUP function to calculate the quarter (1, 2, 3, or 4) for any date. In the worksheet below, the goal i
=ROUNDUP(MONTH(B5)/3,0)This formula works by dividing the month number by 3, then rounding up to the nearest integer. For example, January is month 1, and 1/3 = 0.33, which
="Q"&ROUNDUP(MONTH(B5)/3,0)The easiest way to get the last day of a month is with the EOMONTH function. However, you can also use MONTH with DATE to achieve the same result. In
=DATE(YEAR(B5),MONTH(B5)+1,0)