Excel MONTH Function
Summary
The Excel MONTH function extracts the month from a given date as number between 1 to 12. 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.
Purpose
Get month as a number (1-12) from a date
Return value
A number between 1 and 12.
Syntax
=MONTH (date)
Arguments
- date - A valid Excel date.
Usage notes
The MONTH function extracts the month from a given date as number between 1 to 12. For example:
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. 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 a the number format of your choice.
Notes
- Date must be a valid Excel date.
- MONTH will return a #VALUE error on text values.
MONTH formula examples
To filter and extract data based on multiple complex criteria, you can use the FILTER function with a chain of expressions that use boolean logic. In the example shown, the formula in G5 is:
=FILTER(B5:E16,(LEFT(B5:...
If you want to calculate the quarter (i.e. 1,2,3,4) from a date, you can use the ROUNDUP function together with MONTH.
In the example shown, the formula in cell C5 is:
=ROUNDUP(MONTH(B5)/3,0)
How this formula works...
To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions.
In the example shown, the formula in D5 is:
=DATE(YEAR(B5)+C5,MONTH(B5),...
To generate a series of dates by year, you can use the SEQUENCE function together with YEAR, MONTH, and DAY functions. In the example shown, the formula in E5 is:
=DATE(SEQUENCE(12,1,YEAR(B5)),MONTH(B5),DAY(B5))...
If you need to test two dates to see they both have the same month and year, you can do so with a simple formula that uses the MONTH and YEAR functions.
How the formula works
In the example, the formula in cell D6...
To sum data by month, ignoring year, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, the formula in H6 is:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
The result is a total of all...
If you want to test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions.
In the example shown, the formula in cell C5 is:
=MONTH(DATE(YEAR(B5),2,29...
If you need to extract the month from a date, you can use the MONTH function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date.
How this formula works
The...
If you want to calculate the fiscal quarter from a date, and the fiscal quarter starts in a month other than January, you can use a formula based on the CHOOSE function.
Note: if you just need to calculate a "normal"...
To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions.
How the formula works
In the example, B6...
If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs.
Do you just want to display the month name?
If you only want to display a month...
To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, E5 contains this formula:
=SUMPRODUCT(--(MONTH(birthday)=D5))
This formula counts...
If need to generate a dynamic series of dates with a formula that increase by one year from a single start date, you can do so with a formula that uses the DAY, MONTH, YEAR, and DATE functions.
How the formula works...
To lookup data based on multiple complex criteria, you can use the XLOOKUP function with multiple expressions based on boolean logic. In the example shown, the formula in G5 is:
=XLOOKUP(1,(LEFT(B5:B16)="x...
To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function. In the example shown, the formula in cell D6 is:
=MONTH(B6)=MONTH(C6)
How the formula works...
Related videos
Excel contains special functions that will let you extract the day, month, and year from a valid date. Let's take a look.
Although you can use Excel's AutoFill feature to fill in a series of dates and times, you can also do the same thing with formulas. The advantage of using a formula is that can easily change the starting value and generate a new series. Let's take a look.
In this video, we'll look at at a few examples of the kinds of errors that Excel will flag on a worksheet, and the rules that control the which errors that are flagged.
In this video, we'll look at how to calculate the number of years or months between dates using a function called DATEDIF and a function called YEARFRAC. The DATEDIF function is a "compatability" function that comes originally from Lotus 1-2-3.
In this video, we'll walk through each of the error codes that Excel displays when there's something wrong with a formula. We'll also look at some simple ways to resolve the errors.
In this video, we'll look at how you can use the CHOOSE function. We also compare a formula based on CHOOSE vs. a formula based on VLOOKUP.
Related functions
