The MONTH function is a built-in function in Excel that returns the month part of a date as a number between 1 and 12. For example, if the date is 13/02/2024, the MONTH function will return 2, which represents February.
Basic Syntax
The basic syntax of the MONTH function is:
=MONTH(date)
where date is a valid date value or a cell reference that contains a date.
How to Use the MONTH Function
To use the MONTH function, follow these steps:
- Select a cell where you want to enter the formula.
- Type
=MONTH(and then select a cell that contains a date or type a date value in quotation marks, such as"13/02/2024". - Close the parenthesis and press Enter. The result will be the month part of the date as a number between 1 and 12.
Example
Suppose you have a table of sales data that contains the date, product, and amount columns, as shown below:
| Date | Product | Amount |
|---|---|---|
| 01/01/2024 | A | 100 |
| 15/01/2024 | B | 200 |
| 03/02/2024 | C | 300 |
| 13/02/2024 | A | 400 |
| 28/02/2024 | B | 500 |
| 05/03/2024 | C | 600 |
| 17/03/2024 | A | 700 |
| 31/03/2024 | B | 800 |
You want to use the MONTH function to extract the month part of the date column and create a new column that shows the month name, such as January, February, etc.
To do this, follow these steps:
- Insert a new column after the date column and name it Month.
- In cell B2, enter the formula
=MONTH(A2). This will return 1, which represents January. - Drag the formula down to fill the rest of the column. The result will be the month part of each date as a number between 1 and 12.
- To convert the numbers to month names, you can use the TEXT function, which formats a value based on a given format code. In cell C2, enter the formula
=TEXT(B2,"mmmm"). This will return January, which is the month name corresponding to the number 1. - Drag the formula down to fill the rest of the column. The result will be the month name of each date, as shown below:
| Date | Month | Month Name | Product | Amount |
|---|---|---|---|---|
| 01/01/2024 | 1 | January | A | 100 |
| 15/01/2024 | 1 | January | B | 200 |
| 03/02/2024 | 2 | February | C | 300 |
| 13/02/2024 | 2 | February | A | 400 |
| 28/02/2024 | 2 | February | B | 500 |
| 05/03/2024 | 3 | March | C | 600 |
| 17/03/2024 | 3 | March | A | 700 |
| 31/03/2024 | 3 | March | B | 800 |
Other Approaches
There are other ways to extract the month part of a date in Excel, such as:
- Using the DATEVALUE function, which converts a date in text format to a serial number, and then applying the MONTH function. For example,
=MONTH(DATEVALUE("13/02/2024"))will return 2. - Using the DATE function, which returns a serial number for a given year, month, and day, and then applying the MONTH function. For example,
=MONTH(DATE(2024,2,13))will return 2. - Using the CHOOSE function, which returns a value from a list based on a given index number, and then applying the MONTH function. For example,
=CHOOSE(MONTH(A2),"January","February","March","April","May","June","July","August","September","October","November","December")will return the month name of the date in cell A2.
