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.
Version
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
By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019.
The year function first returns the year from the date in...
The MONTH function takes just one argument, the date from which to extract the month. In the example shown, the formula is:
=MONTH(B4)
where B4 contains the dateJanuary 5, 2016. The MONTH function returns the number...
Working from the inside out, the YEAR, MONTH, and DAY functions extract those respective date components:
=YEAR(B5) // 1960
=MONTH(B5) // 3
=DAY(B5) // 8
At the outer level, the DATE function simply reassembles the...
In the example, B6 is the hard-coded start date and the formula in B7 is:
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then...
In this example, we need to construct logic that filters data to include:
account begins with "x" AND region is "east", and month is NOT April.
The filtering logic of this formula (the include argument) is created by...
This data set contains over 2900 records, and the formula above uses two named ranges:
dates = B5:B2932
amounts = E5:E2932
Inside the SUMPRODUCT function, the MONTH function is used to extract the month number for...
In this case, Excel extracts the month from the date in cell B6 as a number, and the month in the cell C6 as a number, then tests for equivalency using the equal sign. Both dates are in January, so the formula is...
The choose function uses the first argument to "select" remaining elements. For example, in a scheme where 1 = small, 2 = medium, and 3 = large, this formula will "map" the number 2 to "medium".
=CHOOSE(2,"small...
In this case, the formula first extracts the month as a number between 1-12, then divides this number by 3. The result is then rounded up to the nearest whole number using the ROUNDUP function.
The ROUNDUP function...
Do you want to convert the date into a month name?
If you want to convert the date value to a text value, you can use the TEXT function with a custom number format like "mmmm", or "mmm". In the example shown, the...
You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from dates. So, we...
In the example, the formula in cell D6 contains this formula:
=MONTH(B6)&YEAR(B6)=MONTH(C6)&YEAR(C6)
In this case, Excel extracts the month from the date in cell B6 as numbers, then concatenates them...
This formula relies on the FILTER function to retrieve data based on a logical test created with the MONTH function. The array argument is provided as the named range "data", which contains the full set of data without...
Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use boolean logic to...
In the example, B6 is the hard-coded start date and the formula in B7 is:
=DATE(YEAR(B6)+1,MONTH(B6),DAY(B6))
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then...
Related videos
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 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 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 "compatibility" function that originated from Lotus 1-2-3.
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.
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 you can easily change the starting value and generate a new series.
In this video we'll look how to use the FILTER function with Boolean logic to apply multiple criteria. The result is a filtered list where the color is Blue and the month is June
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
