# How to Use EOMONTH Function in Excel (10 Ideal Examples)

In this tutorial, we’ll unpack the EOMONTH function, how to use it, and how to combine it with other functions.

The screenshot above is an overview of what we’ll cover.

## Excel EOMONTH Function: Syntax & Arguments

Function Objective:

The EOMONTH function returns a string of numbers that represent the last day of the month before or after a specified number of months.

Syntax:

`=EOMONTH(start_date, months)`

Argument Explanation:

Argument Required/Optional Explanation
start_date Required The starting date.
months Required The number of months prior to or after the starting date.

Return Parameter:

The last day of the month in the past or future of the specified month.

Version:

The EOMONTH function was introduced in Excel 2007 and is available in all later versions.

## How to Use EOMONTH Function in Excel: 10 Ideal Examples

Suppose we have the dataset below containing Productand Delivery Date columns. Using this dataset, we’ll determine the last days of each month and combine the Excel EOMONTH function with other functions to derive insights from our dataset.

### Example 1 – Basic Usage of the EOMONTH Function

Steps:

• In cell D5, enter the EOMONTH function as follows:

`=EOMONTH(C5,0)`

Here, cell C5 refers to the Delivery Date while the 0 (Zero) indicates the present month. The last day of the month in cell C5 is returned.

### Example 2 – Finding the First and Last Days of the Current Month

Let’s use the EOMONTH function to find the first and last days of the current month.

Steps:

`=TODAY()`

• In cell C6, enter the following formula:

`=EOMONTH(TODAY(),-1)+1`

In this case, the -1 value prompts the EOMONTH function to yield the last day of the previous month. Adding 1 to the result gives the first date of the current month.

• Enter the following formula in cell C7 to retrieve the last day of the current month:

`=EOMONTH(TODAY(),0)`

### Example 3 – Finding the First and Last Days of the Previous Month

In a similar fashion, we can also compute the first and last days of the previous month.

Steps:

`=EOMONTH(C4,-2)+1`

This function returns the last day of the month 2 months ago, then adds 1 to calculate the first day of the following month, which is this month’s previous month.

• In cell C7 cell, enter the formula below to return the last day of the previous month:

`=EOMONTH(C4,-1)`

In this case, C4 represents Today’s Date.

### Example 4 – Calculating the Number of Days in a Month

Combining the DAY and EOMONTH functions can be used to return the number of days in a month.

Steps:

• Enter the following formula in cell C6:

`=DAY(EOMONTH(C4,0))`

• Change the Number Format to General.

The EOMONTH function generates the last day of the month and the DAY function returns the number of days in that month.

### Example 5 – Calculating the Working Days in a Month

By combining the NETWORKDAYS and EOMONTH functions, we can determine the total number of working days in a month.

Steps:

• In cell D5, enter the following formula:

`=NETWORKDAYS(C5, EOMONTH(C5,0),\$B\$14:\$B\$20)`

• Select General as the Number Format.
Formula Breakdown
• NETWORKDAYS(C5, EOMONTH(C5,0),\$B\$14:\$B\$20) → returns the number of whole working days between two dates. Here, C5 is the start_date, EOMONTH(C5,0) is the end_date, and the range \$B\$14:\$B\$20 represents the optional holidays argument that is referenced from the “Holidays” table.
• Output 19

### Example 6 – Computing Monthly Average Sales

By applying the AVERAGEIFS and EOMONTH functions together, we can find an average value between two dates based on a condition.

Steps:

• In cell B15, enter the date 1/1/22.
• Press CTRL + 1.

• In the Format Cells window that opens, choose the Custom tab >> type in the code mmmm (a Month Format) >> click OK.

• In cell C15, enter the following formula:

`=AVERAGEIFS(D5:D12,B5:B12,">="&B15,B5:B12,"<="&EOMONTH(B15,0),C5:C12,"RAM")`

Formula Breakdown
• AVERAGEIFS(D5:D12,B5:B12,”>=”&B15,B5:B12,”<=”&EOMONTH(B15,0),C5:C12,”RAM”) → finds the average for the cells specified by a given set of conditions or criteria. Here, those are:
• D5:D12 is the average_range argument, which is the “Sales” column.
• B5:B12 is the criteria_range1 argument, which refers to the “Date” column, and  “>=”&B15 is the criteria1 argument, which is the value greater than or equal to the first day of “January”.
• B5:B12 is the criteria_range2 argument, which refers to the “Date” column, and “<=”&EOMONTH(B15,0) is the criteria2 argument which represents the values less than or  equal to the last day of “January”.
• C5:C12 is the criteria_range3 argument, while “RAM” is the criteria3 argument that indicates the “Sales” of “Ram”.
• Output → \$4,214

### Example 7 – Finding the Number of Monthly Occurrences

We can check for the number of occurrences of a value between two dates by means of the COUNTIF and EOMONTH functions.

Steps:

`=COUNTIF(B5:B12,">"&B15)-COUNTIF(B5:B12,">"&EOMONTH(B15,0))`

Formula Breakdown
• COUNTIF(B5:B12,”>”&B15) →  counts the number of cells within a range that meet the given condition. Here, B5:B12 represents the range argument that refers to the Date column, while “>”&B15 indicates the criteria argument that returns the count of the matched value.
• Output → 6
• COUNTIF(B5:B12,”>”&EOMONTH(B15,0)) →  here, B5:B12 represents the range argument that refers to the Date column, while “>”&EOMONTH(B15,0) indicates the criteria argument that returns the count of the matches.
• Output → 3
• COUNTIF(B5:B12,”>”&B15)-COUNTIF(B5:B12,”>”&EOMONTH(B15,0)) →  becomes
• 6 – 3 → 3

### Example 8 – Retrieving Total Monthly Sales

We can also determine the Total Sales using the SUMIFS and EOMONTH functions

Steps:

• Follow the steps above to enter the formatted month names in cells B15:B17.
• Enter the following formula in cell C15:

`=SUMIFS(\$D\$5:\$D\$12,\$B\$5:\$B\$12,">="&B15,\$B\$5:\$B\$12,"<="&EOMONTH(B15,0))`

Formula Breakdown
• SUMIFS(\$D\$5:\$D\$12,\$B\$5:\$B\$12,”>=”&B15,\$B\$5:\$B\$12,”<=”&EOMONTH(B15,0)) → adds the cells specified by a given set of conditions or criteria. Here, \$D\$5:\$D\$12 is the sum_range argument that refers to the “Sales” column, \$B\$5:\$B\$12 represents the criteria_range_1 argument that points to the “Date” column, and “>=”&B15 is the criteria_1 argument representing the values greater than or equal to the first day of “February”. Next, \$B\$5:\$B\$12 is the criteria_range_2 argument which indicates the “Date” column and “<=”&EOMONTH(B15,0) is the criteria_2 argument representing the values less than or equal to the last day of “February”.
• Output → \$13,325

### Example 9 – Checking If a Date Is Within the Next n Months

We can use the AND function in conjunction with the EOMONTH function to check if a date is within the next n months. Here, we’ll set the n to 4 months in the future.

Steps:

• Enter the TODAY function in cell C15:

`=TODAY()`

• Enter the formula below in cell D5:

`=AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),4))`

Formula Breakdown
• AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),4)) → checks whether all the arguments are TRUE, and returns TRUE if they are. C5>EOMONTH(TODAY(),0) is the logical1 argument that checks if the date in cell C5 is greater than today’s date, and C5<=EOMONTH(TODAY(),4) is the logical2 argument that checks whether the date in cell C5 is less than the last day of the month 4 months in the future. Since the first argument is FALSE and the second argument is TRUE, the AND function returns the output FALSE.
• Output → FALSE

### Example 10 – Categorizing Payments Based on Months

We can prioritize outstanding payments by grouping them with the IF, DATE, and EOMONTH functions.

Steps:

• Enter the DATE function below in cell C14 to obtain today’s date:

`=DATE(2022,12,28)`

In this case, 2022, 12, and 28 point to the year, month, and day arguments respectively.

Note: You can open the Format Cells dialog box by pressing CTRL + 1 and change the cell formatting to Short Date.

• In cell E6, enter the formula below, press Enter, then drag the Fill Handle tool to copy the formula to the cells below:

`=IF(B6-EOMONTH(\$C\$14,0)<=30,D6,"")`

Formula Breakdown
• EOMONTH(\$C\$14,0) → returns the serial number of the last day of the month before or after a specified number of months. Here, C14 is the start_date argument, and 0 (zero) is the months argument, representing the current month.
• Output12/31/2022
• IF(B6-EOMONTH(\$C\$14,0)<=30,D6,””) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, B6-EOMONTH(\$C\$14,0)<=30 is the logical_test argument that checks if the difference between the dates B6-EOMONTH(\$C\$14,0) is less than or equal to 30. If true, the function returns D6 (the value_if_true argument), otherwise it returns “” (Blank) (the value_if_false argument).
• Output → “” (Blank)

• In cell F6, enter the formula below:

`=IF(AND(B6-EOMONTH(\$C\$14,0)>=31,B6-EOMONTH(\$C\$14,0)<=60),D6,"")`

Formula Breakdown
• AND(B6-EOMONTH(\$C\$14,0)>=31,B6-EOMONTH(\$C\$14,0)<=60) → Here, B6-EOMONTH(\$C\$14,0)>=31 is the logical1 argument that checks if the difference between the two dates is greater than 31, and B6-EOMONTH(\$C\$14,0)<=60 is the logical2 argument that checks whether the difference between the two dates is less than 60. Since the first argument is TRUE and the second argument is FALSE, the AND function returns FALSE.
• Output → FALSE
• IF(AND(B6-EOMONTH(\$C\$14,0)>=31,B6-EOMONTH(\$C\$14,0)<=60),D6,””) → becomes
• IF(FALSE,D6,””) → The FALSE value of the logical_test argument prompts the function to return “” (Blank) (value_if_false argument).
• Output → “” (Blank)

• Enter the following formula in cell G6:

`=IF(B6-EOMONTH(\$C\$14,0)>=61,D6,"")`

## How to Determine the End Date of a Future Month Using EoMonth in Excel VBA

Last but not least, we can determine the end date of a future month using the EoMonth function in VBA. On this occasion, we’ll compute the last date of the month 6 months into the future.

Steps:

• Go to the Developer tab >> click the Visual Basic button.

The Visual Basic Editor opens in a new window.

• Go to the Insert tab >> select Module.

Copy the code below and paste it into the window:

``````Sub End_Date_of_future_month()

Dim r_range As Range
Set r_range = Selection
Dim selected_cells As Range

For Each selected_cells In r_range
selected_cells.Offset(0, 1) = Application.WorksheetFunction.EoMonth(selected_cells, 6)
Next selected_cells

End Sub``````

Code Breakdown
• In the first portion, the sub-routine is given a name, End_Date_of_future_month().
• Next, the variables r_range and selected_cells are defined and assigned as type Range.
• A For Loop is used to iterate through the cells in the r_range while applying the EoMonth function to return the end date and the Offset property to paste the results into the adjacent column.

• Close the VBA window >> select the range C5:C13 >> click the Macros button >> hit Run.

The results should look like the screenshot below.

## Common Errors While Using the EOMONTH Function in Excel

There are several errors we may encounter while using the EOMONTH function.

Error Reason
#NUM! The start_date argument is an invalid date.
#VALUE! The start_date argument is text.
• The function will return the #NUM! Error if the start_date argument contains an invalid Date format, like 23-23-22.

• Additionally, we will face the #VALUE! Error if the start_date argument is a non-numeric value, for instance, cell C4 refers to the text Delivery Date.

## Things to Remember

• A positive number in the months argument returns dates in the future, whereas a negative number yields dates in the past.
• The EOMONTH function ignores any decimal values given in the start_date and months arguments.
• By default, the EOMONTH function generates the date as a serial number that needs to be formatted as a Date.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!