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

Certainly, while working in Excel, we often have dates in the dataset and luckily, Excel has a palette of functions for dealing with dates in the dataset. In this tutorial, we’ll learn about how to use the EOMONTH function in Excel and combine it with other functions. Moreover, we’ll also explore its quirks and features.

In this scenario, the above screenshot is an overview of the article, which represents the application of the Excel EOMONTH function. In the following sections, you’ll learn more about the dataset as well as how to use the function.

Excel EOMONTH Function: Syntax & Arguments

Function Objective:

Simply put, 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:

Argument Explanation:

Return Parameter:

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

Version:

For one thing, the EOMONTH function was introduced in Excel 2007 and is available in all versions after that.

How to Use EOMONTH Function in Excel: 10 Ideal Examples

First and foremost, let’s consider the List of Product and Delivery Dates dataset shown in the B4:C13 cells containing the “Product” and “Delivery Date” columns. Here, we want to determine the last days of each month and combine the Excel EOMONTH function with other functions to derive valuable insights from our dataset. Henceforth, without further delay, let’s see each example in detail and with the necessary illustrations.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

Example 1: Basic Usage of EOMONTH Function

In the first place, let’s start off with the basic usage of the EOMONTH function and move to more complex examples thereupon.

📌 Steps:

• Initially, go to the D5 cell >> type in the EOMONTH function and enter the two arguments.

Here, the C5 cell refers to the “Delivery Date” while the 0 (Zero) indicates the present month.

Example 2: Finding First and Last Days of the Current Month

Besides, we can find the first and last days of the current month using Excel’s EOMONTH function.

📌 Steps:

• Initially, move to the C4 cell >> apply the TODAY function to return today’s date.

• Next, jump to the C6 cell >> use the formula given below.

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

• Then, insert the expression below into the C7 cell to retrieve the last of the current month.

Example 3: Obtaining 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, so just follow along.

📌 Steps:

• In the first place, get today’s date as shown previously >> enter the equation in the C6 cell.

For instance, the function returns the last day 2 months prior, then adds 1 to calculate the first day of the previous month.

• In turn, enter the C7 cell >> use the formula below to procure the last of the previous month.

In this case, the C4 cell represents “Today’s Date”.

Example 4: Calculating Number of Days in a Month

By the same token, the DAY and EOMONTH functions can return the number of days in a month.

📌 Steps:

• To start with, copy and paste the expression into the C6 cell >> change the Number Format to General.

On this occasion, the EOMONTH function generates the last of the month and the DAY function returns the number of days in that month.

Example 5: Returning the Working Days in a Month

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

📌 Steps:

• At the very beginning, navigate to the D5 cell >> insert the following formula into the Formula Bar >> select General as the Number Format.

Example 6: Computing Monthly Average Sales

In addition to this, applying the AVERAGEIFS and EOMONTH functions can allow us to obtain an average value between two dates based on a condition.

📌 Steps:

• First of all, proceed to the B15 cell >> enter the date “1/1/22” >> hit the CTRL + 1 keys.

• Now, in the Format Cells window, choose the Custom tab >> type in the code mmmm which points to the Month Format >> press OK.

• Finally, jump to the C15 cell >> use the following expression.

Example 7: Yielding Number of Monthly Occurrences

Alternatively, we can check for the number of occurrences of a value between two dates with the help of the COUNTIF and EOMONTH functions.

📌 Steps:

• First and foremost, enter the date and apply custom formatting in the B15 cell as shown before >> copy and paste the equation into the C15 cell.

Example 8: Retrieving Total Monthly Sales

Conversely, we can also determine the “Total Sales” using the SUMIFS and EOMONTH functions

📌 Steps:

• Initially, follow the prior steps to enter the formatted month names in the B15:B17 cells >> type in the formula in the C15 cell.

Example 9: Checking If Date Is Within Next n Months

For one thing, we can use the AND 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:

• To begin with, apply the TODAY function to return today’s date in the C15 cell.

• Following this, enter the expression below into the D5 cell.

Example 10: Categorizing Payments Based on Months

Furthermore, we can prioritize outstanding payments with IF, DATE, and EOMONTH functions by listing them into groups. This means, that we can keep track of the debts that need to be paid first, due to the approaching deadline.

📌 Steps:

• At the start, type in the DATE function in the C14 cell to obtain today’s date.

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 changing the cell formatting to Short Date.

• From this point, move to the E6 cell >> enter the expression given below >>  >> drag the Fill Handle tool to copy the formula to the cells below.

• Not long after, navigate to the F6 cell >> copy and paste the formula below.

• Eventually, enter the following equation into the G6 cell.

How to Determine 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 corresponding to 6 months into the future.

📌 Steps:

• First, navigate to the Developer tab >> click the Visual Basic button.

Now, this opens the Visual Basic Editor in a new window.

• Second, go to the Insert tab >> select Module.

For ease of reference, copy the code from here and paste it into the window as shown below.

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

• Third, close the VBA window >> select the C5:C13 cells >> click the Macros button >> hit Run.

Eventually, the results should look like the screenshot given below.

Admittedly, we have skipped some relevant examples of using the EoMonth function in VBA, which you may explore if you wish.

Common Errors While Using the EOMONTH Function in Excel

Furthermore, in this section, we’ll discuss the possible errors we may encounter while using the EOMONTH function in Excel.

• In the first place, the function may return the #NUM! Error if the start_date argument contains an invalid Date format, like “23-23-22”.

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

Things to Remember

Here are a few things to note when using the EOMONTH function in Excel.

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

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Download Practice Workbook

Conclusion

In essence, this article shows 10 effective ways to use the EOMONTH function in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

<< Go Back to Excel Functions | Learn Excel