The MONTH function returns the month of a date as a number from 1 (January) to 12 (December). It is a simple way to extract just the month part from a full date value.
=MONTH(serial_number)What does MONTH do?
The MONTH function is a built-in Excel date and time function that takes a date and gives you back just the month as a number. For example, if you give it the date 15 March 2024, it returns 3 because March is the third month. This is useful whenever you need to group, sort, or analyse data by month without caring about the exact day or year.
Dates in Excel are stored as serial numbers, which makes them hard to read at a glance. MONTH cuts through that by isolating the month component. You can use it to build monthly summaries, filter records for a specific month, or create labels like "Month 3" for reports. It works with any valid Excel date, whether you type it directly, reference a cell, or produce it with another function like DATE or TODAY.
Imagine you have a list of invoice dates and you want to know how many invoices were issued in each month. Instead of manually checking each date, you can add a helper column with =MONTH(A2) and then use that column in a pivot table or COUNTIF formula. MONTH turns a messy date column into clean, analysable month numbers in seconds.
When to use MONTH
- Grouping sales or expenses by month for monthly reports.
- Filtering a list of dates to show only records from a particular month.
- Creating a helper column to sort data chronologically by month.
- Building labels such as "Month 1" through "Month 12" for dashboards.
- Calculating the number of months between two dates using other functions together with MONTH.
- Extracting the month from timestamps to analyse seasonal trends.
MONTH syntax
=MONTH(serial_number)| Argument | Required? | What to enter |
|---|---|---|
serial_number | Required | The date you want the month from. It can be a cell reference, a date typed in quotes like "2024-03-15", or a number produced by another function. For example, =MONTH("2024-03-15") returns 3. |
What it returns: A whole number from 1 to 12 representing the month of the given date. January returns 1, December returns 12.
MONTH examples
1 Example 1: Extract the month from a single date
This is the simplest use case. The goal is to find out which month a specific date falls in. The formula =MONTH(B2) looks at the date in cell B2 and returns its month number. Because B2 contains 2024-03-15, the formula returns 3 for March. You can use this to quickly check the month of any date without counting on your fingers.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Event | Date | |||
| 2 | Conference | 2024-03-15 | 3 | ||
| 3 | Workshop | 2024-07-22 | |||
| 4 | Webinar | 2024-11-05 | |||
| 5 |
2 Example 2: Month number for a list of order dates
Here we have a small table of orders with their dates. The goal is to add a helper column that shows the month number for each order. The formula =MONTH(B5) refers to the date in row 5 and returns 12 because that date is in December. You can copy this formula down the column to get the month for every order. This makes it easy to sort or filter orders by month later.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order ID | Order Date | |||
| 2 | ORD-001 | 2024-01-10 | 12 | ||
| 3 | ORD-002 | 2024-02-14 | |||
| 4 | ORD-003 | 2024-03-20 | |||
| 5 | ORD-004 | 2024-12-01 | |||
| 6 | ORD-005 | 2024-05-30 | |||
| 7 |
3 Example 3: Find the month from a text date
Sometimes dates are stored as text rather than real Excel dates. The MONTH function can still handle many text dates if Excel recognises them. The formula =MONTH(B3) takes the text "2024-08-19" from cell B3 and returns 8 for August. This is handy when you import data from other systems. Just be aware that if Excel cannot interpret the text as a date, you will get a #VALUE! error.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Due Date | |||
| 2 | Report | 2024-06-30 | 8 | ||
| 3 | Presentation | 2024-08-19 | |||
| 4 | Review | 2024-10-12 | |||
| 5 |
4 Example 5: Count how many sales happened in a specific month
This example uses MONTH inside a SUMPRODUCT formula to count records for a given month. The goal is to count how many sales occurred in March (month 3). The formula =SUMPRODUCT(--(MONTH(B2:B6)=3)) checks each date in B2:B6, converts TRUE/FALSE to 1/0, and sums them. Because only 2024-03-10 is in March, the result is 1. This pattern is very useful for monthly reporting without helper columns.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sale ID | Sale Date | |||
| 2 | S-101 | 2024-01-05 | 1 | ||
| 3 | S-102 | 2024-03-10 | |||
| 4 | S-103 | 2024-04-15 | |||
| 5 | S-104 | 2024-06-20 | |||
| 6 | S-105 | 2024-07-25 | |||
| 7 |
Common MONTH errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The serial_number argument is text that Excel cannot recognise as a date, such as "March 2024" without a day, or a non-date string like "hello". | Make sure the cell contains a valid date. If it is text, convert it to a real date using DATEVALUE or by re-entering it in a recognised format. |
| #NUM! | The serial_number is a number that is negative or too large to be a valid Excel date (dates before 1900 or after 9999). | Check that the value is a positive number within Excel's date range. Use a valid date instead. |
| #NAME? | The function name is misspelled, for example =MONTHS(A2) or =MONTH(A2. | Type =MONTH( exactly, and make sure parentheses are balanced. |
| Wrong month returned | The cell contains a date in a different date system or the date was entered as text that Excel interpreted differently (e.g., US vs UK format). | Verify the date is stored as a real date. Use the DATE function to build dates unambiguously, or check your regional date settings. |
Tips for using MONTH
- Use MONTH together with TEXT if you want the month name instead of a number: =TEXT(A2,"mmmm") gives "March".
- To get the month number with a leading zero, use =TEXT(MONTH(A2),"00").
- MONTH ignores the day and year, so it is perfect for grouping by month regardless of the year.
- If your dates include times, MONTH still works because it only looks at the date part.
- Combine MONTH with COUNTIF or SUMPRODUCT to build monthly summaries without pivot tables.
- Remember that MONTH returns a number, not text, so you can use it in further calculations.
Frequently asked questions
What does the MONTH function return for January?
It returns 1, because January is the first month of the year. December returns 12.
Can MONTH return the month name instead of a number?
No, MONTH always returns a number from 1 to 12. To get the month name, wrap it in TEXT: =TEXT(A2,"mmmm").
Why does MONTH give a #VALUE! error?
This usually happens when the cell contains text that Excel does not recognise as a date. Make sure the cell holds a valid date or use DATEVALUE to convert text to a date.
Does MONTH work with dates that include time?
Yes. MONTH only looks at the date portion, so a value like 2024-03-15 14:30 returns 3.
How can I count how many dates fall in a specific month?
Use =SUMPRODUCT(--(MONTH(range)=month_number)). For example, =SUMPRODUCT(--(MONTH(A2:A100)=3)) counts all dates in March.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MONTH reference