MONTH Function in Microsoft Excel
Part 1: Introduction
Definition
The MONTH function in Microsoft Excel is a date/time function that returns the month of a given date as an integer, ranging from 1 (January) to 12 (December).
Purpose
The purpose of the MONTH function is to extract the month from a specified date.
Syntax & Arguments
The syntax for the MONTH function is as follows:
MONTH(serial_number) serial_number: This is a required argument. It represents the date of the month you are trying to find. Dates should be entered using the DATE function or as results of other formulas or functions. For example, useDATE(2008,5,23)for the 23rd day of May 2008. Problems can occur if dates are entered as text.
Return Value
The MONTH function returns the month of a date represented by a serial number as an integer, ranging from 1 (January) to 12 (December).
Remarks
Microsoft Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900. The values returned by the YEAR, MONTH, and DAY functions will be Gregorian values regardless of the display format for the supplied date value.
Part 2: Examples
Let’s look at some examples of using the MONTH function in business.
Example 1
Purpose: To find the month of the invoice date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Invoice Date | Month |
| 2 | 2023-05-15 | =MONTH(A2) |
| 3 | 2023-06-20 | =MONTH(A3) |
| 4 | 2023-07-25 | =MONTH(A4) |
Explanation: In this example, we have invoice dates and want to find the month of these dates. The formula =MONTH(A2) returns the month of the date in cell A2. The results would be 5, 6, and 7 for May, June, and July, respectively.
Example 2
Purpose: To categorize sales data by the month of the sale date.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Sale Date | Sales | Month |
| 2 | 2023-01-15 | 5000 | =MONTH(A2) |
| 3 | 2023-02-20 | 6000 | =MONTH(A3) |
| 4 | 2023-03-25 | 7000 | =MONTH(A4) |
Explanation: In this example, we have sales data, and we want to categorize this data by the month of the sale date. The formula =MONTH(A2) returns the month of the date in cell A2. The results would be 1, 2, and 3 for January, February, and March, respectively.
Example 3
Purpose: To track the month of the received payments.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Payment Date | Amount | Month |
| 2 | 2023-04-15 | 1500 | =MONTH(A2) |
| 3 | 2023-05-20 | 2000 | =MONTH(A3) |
| 4 | 2023-06-25 | 2500 | =MONTH(A4) |
Explanation: In this example, we have payment dates and want to track the month of these payments. The formula =MONTH(A2) returns the month of the date in cell A2. The results would be 4, 5, and 6 for April, May, and June, respectively.
Example 4
Purpose: To determine the month of the contract start date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Contract Start | Month |
| 2 | 2023-07-01 | =MONTH(A2) |
| 3 | 2023-08-15 | =MONTH(A3) |
| 4 | 2023-09-30 | =MONTH(A4) |
Explanation: In this example, we have contract start dates and want to determine the month of these dates. The formula =MONTH(A2) returns the month of the date in cell A2. The results would be 7, 8, and 9 for July, August, and September, respectively.
Example 5
Purpose: To find the month of the delivery date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Delivery Date | Month |
| 2 | 2023-10-15 | =MONTH(A2) |
| 3 | 2023-11-20 | =MONTH(A3) |
| 4 | 2023-12-25 | =MONTH(A4) |
Explanation: In this example, we have delivery dates and want to find the month of these dates. The formula =MONTH(A2) returns the month of the date in cell A2. The results would be 10, 11, and 12 for October, November, and December, respectively.
Example 6: Using MONTH with IF Function
Purpose: To classify fiscal quarters based on the month of the sale date.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Sale Date | Sales | Quarter |
| 2 | 2023-01-15 | 5000 | =IF(MONTH(A2)<=3, “Q1”, IF(MONTH(A2)<=6, “Q2”, IF(MONTH(A2)<=9, “Q3”, “Q4”))) |
| 3 | 2023-04-20 | 6000 | =IF(MONTH(A3)<=3, “Q1”, IF(MONTH(A3)<=6, “Q2”, IF(MONTH(A3)<=9, “Q3”, “Q4”))) |
| 4 | 2023-07-25 | 7000 | =IF(MONTH(A4)<=3, “Q1”, IF(MONTH(A4)<=6, “Q2”, IF(MONTH(A4)<=9, “Q3”, “Q4”))) |
Explanation: In this example, we have sales data, and we want to classify this data into fiscal quarters based on the month of the sale date. The nested IF function with the MONTH function helps to organize the data into “Q1”, “Q2”, “Q3”, and “Q4”.
Example 7: Using MONTH with SUM Function
Purpose: To calculate the total sales for a specific month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-01-20 | 6000 |
| 4 | 2023-02-25 | 7000 |
| 5 | 2023-01-30 | 8000 |
| 6 | Total January Sales | =SUM(IF(MONTH(A2:A5)=1, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to calculate the total sales for January. The SUM function combined with the MONTH function helps to sum the sales for January.
Example 8: Using MONTH with VLOOKUP Function
Purpose: To find the sales data for a specific month from a lookup table.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | January | 5000 |
| 3 | February | 6000 |
| 4 | March | 7000 |
| 5 | April | 8000 |
| 6 | Sale Date | 2023-03-15 |
| 7 | Sales for Sale Date Month | =VLOOKUP(TEXT(MONTH(A6), “mmmm”), A2:B5, 2, FALSE) |
Explanation: In this example, we have a lookup table with monthly sales data. We want to find the sales for the month of a specific sale date. The VLOOKUP function combined with the MONTH function helps to find the deals for the month of the sale date.
Example 9: Using MONTH with AVERAGE Function
Purpose: To calculate the average sales for a specific month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-01-20 | 6000 |
| 4 | 2023-02-25 | 7000 |
| 5 | 2023-01-30 | 8000 |
| 6 | Average January Sales | =AVERAGE(IF(MONTH(A2:A5)=1, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to calculate the average sales for January. The AVERAGE function combined with the MONTH function helps to estimate the average sales for January.
Example 10: Using MONTH with COUNTIF Function
Purpose: To count the number of sales made in a specific month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-01-20 | 6000 |
| 4 | 2023-02-25 | 7000 |
| 5 | 2023-01-30 | 8000 |
| 6 | Number of January Sales | =COUNTIF(A2:A5, “>=”&DATE(2023,1,1), A2:A5, “<“&DATE(2023,2,1)) |
Explanation: In this example, we have sales data and want to count the number of sales made in January. The COUNTIF function combined with the MONTH function helps to count the number of sales made in January.
Example 11: Using MONTH with MAX Function
Purpose: To find the maximum sales made in a specific month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-01-20 | 6000 |
| 4 | 2023-02-25 | 7000 |
| 5 | 2023-01-30 | 8000 |
| 6 | Maximum January Sales | =MAX(IF(MONTH(A2:A5)=1, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to find the maximum sales made in January. The MAX function combined with the MONTH function helps to find the top deals made in January.
Example 12: Using MONTH with MIN Function
Purpose: To find the minimum sales made in a specific month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-01-20 | 6000 |
| 4 | 2023-02-25 | 7000 |
| 5 | 2023-01-30 | 8000 |
| 6 | Minimum January Sales | =MIN(IF(MONTH(A2:A5)=1, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to find the minimum sales made in January. The MIN function combined with the MONTH function helps to find the minimum deals made in January.
Part 3: Tips and Tricks
The MONTH function can be combined to create more complex formulas. For example, you can use it with the IF function to perform actions based on the month of a date.
Remember that Excel stores date as sequential serial numbers so that you can use these numbers in calculations.
Be careful when entering dates as text. Using the DATE function or the results of other formulas or functions is recommended to avoid any issues.
If you want to return the month name instead of the month number, you can use the TEXT function in combination with the MONTH function, like this:
=TEXT(A2, "mmmm").The MONTH function will return Gregorian values regardless of the display format for the supplied date value.