# 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:

syntax
```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:

ABC
1Start DateMonthsEnd of Month
22023-07-290=EOMONTH(A2, B2)
32023-08-150=EOMONTH(A3, B3)
42023-09-010=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:

ABC
1Start DateMonthsEnd of Month
22023-07-293=EOMONTH(A2, B2)
32023-08-156=EOMONTH(A3, B3)
42023-09-019=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:

ABC
1Start DateMonthsEnd of Month
22023-07-29-3=EOMONTH(A2, B2)
32023-08-15-6=EOMONTH(A3, B3)
42023-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:

ABCD
1Start DateMonthsPrincipalMaturity Date
22023-07-2961000=EOMONTH(A2, B2)
32023-08-1592000=EOMONTH(A3, B3)
42023-09-01123000=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:

ABCD
1Bill DatePayment Term (Months)AmountDue Date
22023-07-2911000=EOMONTH(A2, B2)
32023-08-1522000=EOMONTH(A3, B3)
42023-09-0133000=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.

ABC
1Start DateFormulaResult
22023-07-01=IF(WEEKDAY(EOMONTH(A2, 0), 2) > 5, “Weekend”, “Weekday”)Weekday
32023-07-15=IF(WEEKDAY(EOMONTH(A3, 0), 2) > 5, “Weekend”, “Weekday”)Weekday
42023-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.

ABC
1DateSalesSales at EOM
22023-07-01500=VLOOKUP(EOMONTH(A2, 0), A2:B4, 2, FALSE)
32023-07-15300=VLOOKUP(EOMONTH(A3, 0), A2:B4, 2, FALSE)
42023-07-30200=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.

ABC
1DateSalesSales at EOM
22023-07-01500=COUNTIF(A2:A4, EOMONTH(A2, 0))
32023-07-15300=COUNTIF(A2:A4, EOMONTH(A3, 0))
42023-07-30200=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.

ABC
1DateSalesAvg Sales at EOM
22023-07-01500=AVERAGEIF(A2:A4, EOMONTH(A2, 0), B2:B4)
32023-07-15300=AVERAGEIF(A2:A4, EOMONTH(A3, 0), B2:B4)
42023-07-30200=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.

ABC
1DateSalesTotal Sales at EOM
22023-07-01500=SUMIF(A2:A4, EOMONTH(A2, 0), B2:B4)
32023-07-15300=SUMIF(A2:A4, EOMONTH(A3, 0), B2:B4)
42023-07-30200=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

1. 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.
2. Remember that a positive value for the month’s argument yields a future date, while a negative value yields a past date.
3. 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.
4. 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.
5. Remember that Excel stores date as sequential serial numbers for calculations. By default, January 1, 1900, is serial number 1.