How to Use the EOMONTH Function in Microsoft Excel
Part 1: Introduction
Definition
The EOMONTH function in Microsoft Excel returns the serial number for the last day of the month, which is the indicated number of months before or after a specified start date.
Purpose
The EOMONTH function calculates maturity dates or due dates that fall on the last day of the month.
Syntax & Arguments
The syntax for the EOMONTH function is as follows:
EOMONTH(start_date, months) The EOMONTH function has the following arguments:
start_date: This is a required argument. It is a date that represents the starting date. Dates should be entered using the DATE function or as results of other formulas or functions.
months: This is also a required argument. It is the number of months before or after the start_date. A positive value for months yields a future date; a negative value yields a past date.
Return Value
The EOMONTH function returns the serial number of the last day of the month, before or after a certain number of months from the start date.
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.
- If start_date is not a valid date, EOMONTH returns the #NUM! Error value.
- If start_date plus months yields an invalid date, EOMONTH returns the #NUM! Error value.
Part 2: Examples
Example 1: Calculate the end of the month for a given date
Purpose: To calculate the end of the month for a given date.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Months | End of Month |
| 2 | 2023-07-29 | 0 | =EOMONTH(A2, B2) |
| 3 | 2023-08-15 | 0 | =EOMONTH(A3, B3) |
| 4 | 2023-09-01 | 0 | =EOMONTH(A4, B4) |
Explanation: In this example, we want to calculate the end of the month for a given date. The formula =EOMONTH(A2, B2) returns the end of the month for the date in cell A2.
Example 2: Calculate the end of the month after a certain number of months
Purpose: To calculate the end of the month after a certain number of months.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Months | End of Month |
| 2 | 2023-07-29 | 3 | =EOMONTH(A2, B2) |
| 3 | 2023-08-15 | 6 | =EOMONTH(A3, B3) |
| 4 | 2023-09-01 | 9 | =EOMONTH(A4, B4) |
Explanation: In this example, we want to calculate the end of the month after a certain number of months. The formula =EOMONTH(A2, B2) returns at the end of the month which is 3 months after the start date.
Example 3: Calculate the end of the month before a certain number of months
Purpose: To calculate the end of the month before a certain number of months.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Months | End of Month |
| 2 | 2023-07-29 | -3 | =EOMONTH(A2, B2) |
| 3 | 2023-08-15 | -6 | =EOMONTH(A3, B3) |
| 4 | 2023-09-01 | -9 | =EOMONTH(A4, B4) |
Explanation: In this example, we want to calculate the end of the month before a certain number of months. The formula =EOMONTH(A2, B2) returns the end of the month, which is 3 months before the start date.
Example 4: Calculate the maturity date of a term deposit
Purpose: To calculate the maturity date of a term deposit.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | Months | Principal | Maturity Date |
| 2 | 2023-07-29 | 6 | 1000 | =EOMONTH(A2, B2) |
| 3 | 2023-08-15 | 9 | 2000 | =EOMONTH(A3, B3) |
| 4 | 2023-09-01 | 12 | 3000 | =EOMONTH(A4, B4) |
Explanation: In this example, we want to calculate the maturity date of a term deposit. The formula =EOMONTH(A2, B2) returns the date 6 months after the start date, which is the maturity date of the term deposit.
Example 5: Calculate the due date of a bill
Purpose: To calculate the due date of an invoice.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Bill Date | Payment Term (Months) | Amount | Due Date |
| 2 | 2023-07-29 | 1 | 1000 | =EOMONTH(A2, B2) |
| 3 | 2023-08-15 | 2 | 2000 | =EOMONTH(A3, B3) |
| 4 | 2023-09-01 | 3 | 3000 | =EOMONTH(A4, B4) |
Explanation: In this example, we want to calculate a bill’s due date. The formula =EOMONTH(A2, B2) returns the date 1 month after the bill date, which is the bill’s due date.
Example 6: EOMONTH with IF Function
Purpose of Example: To determine if the end of the month is a weekend.
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Formula | Result |
| 2 | 2023-07-01 | =IF(WEEKDAY(EOMONTH(A2, 0), 2) > 5, “Weekend”, “Weekday”) | Weekday |
| 3 | 2023-07-15 | =IF(WEEKDAY(EOMONTH(A3, 0), 2) > 5, “Weekend”, “Weekday”) | Weekday |
| 4 | 2023-07-30 | =IF(WEEKDAY(EOMONTH(A4, 0), 2) > 5, “Weekend”, “Weekday”) | Weekday |
Explanation: The formula checks if the end of the month of the given date falls on a weekend (Saturday or Sunday). If it does, it returns “Weekend”; otherwise, it replaces “Weekday”. In all these examples, the end of the month (July 31, 2023) is a weekday.
Example 8: EOMONTH with VLOOKUP Function
Purpose of Example: To find the sales data for the end of the month.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Sales | Sales at EOM |
| 2 | 2023-07-01 | 500 | =VLOOKUP(EOMONTH(A2, 0), A2:B4, 2, FALSE) |
| 3 | 2023-07-15 | 300 | =VLOOKUP(EOMONTH(A3, 0), A2:B4, 2, FALSE) |
| 4 | 2023-07-30 | 200 | =VLOOKUP(EOMONTH(A4, 0), A2:B4, 2, FALSE) |
Explanation: The formula uses VLOOKUP to find the sales data for the end of the month on the given date. If there’s no exact match, it returns an error.
Example 9: EOMONTH with COUNTIF Function
Purpose of Example: To count the number of sales that happened at the end of the month.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Sales | Sales at EOM |
| 2 | 2023-07-01 | 500 | =COUNTIF(A2:A4, EOMONTH(A2, 0)) |
| 3 | 2023-07-15 | 300 | =COUNTIF(A2:A4, EOMONTH(A3, 0)) |
| 4 | 2023-07-30 | 200 | =COUNTIF(A2:A4, EOMONTH(A4, 0)) |
Explanation: The formula counts the number of sales that happened at the end of the month of the given date. In this example, there’s only one sale at the end of the month (July 30, 2023).
Example 10: EOMONTH with AVERAGEIF Function
Purpose of Example: To calculate the average sales for the end of the month.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Sales | Avg Sales at EOM |
| 2 | 2023-07-01 | 500 | =AVERAGEIF(A2:A4, EOMONTH(A2, 0), B2:B4) |
| 3 | 2023-07-15 | 300 | =AVERAGEIF(A2:A4, EOMONTH(A3, 0), B2:B4) |
| 4 | 2023-07-30 | 200 | =AVERAGEIF(A2:A4, EOMONTH(A4, 0), B2:B4) |
Explanation: The formula calculates the average sales for the end of the month of the given date. In this example, the average sales at the end of the month (July 30, 2023) is 200.
Example 13: EOMONTH with SUMIF Function
Purpose of Example: To calculate the total sales for the end of the month.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Sales | Total Sales at EOM |
| 2 | 2023-07-01 | 500 | =SUMIF(A2:A4, EOMONTH(A2, 0), B2:B4) |
| 3 | 2023-07-15 | 300 | =SUMIF(A2:A4, EOMONTH(A3, 0), B2:B4) |
| 4 | 2023-07-30 | 200 | =SUMIF(A2:A4, EOMONTH(A4, 0), B2:B4) |
Explanation: The formula calculates the total sales for the end of the month of the given date. In this example, the total sales at the end of the month (July 30, 2023) is 200.
Part 3: Tips and Tricks
- Use the EOMONTH function to calculate the end of the month for a given date or the end of the month after or before a certain number of months.
- Remember that a positive value for the month’s argument yields a future date, while a negative value yields a past date.
- Be aware that if the start_date or the result of start_date plus months is not a valid date, EOMONTH will return the #NUM! Error value.
- Use the EOMONTH function with other parts to calculate maturity dates, due dates, and different types of dates that fall on the last day of the month.
- Remember that Excel stores date as sequential serial numbers for calculations. By default, January 1, 1900, is serial number 1.