Eshrak Kader
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.
Download Practice Workbook
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:
=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 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.
10 Examples of Using EOMONTH Function in Excel
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.
=EOMONTH(C5,0)
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 TODAY function to return today’s date.
=TODAY()
- Next, jump to the C6 cell >> use the formula given below.
=EOMONTH(TODAY(),-1)+1
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.
=EOMONTH(TODAY(),0)
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.
=EOMONTH(C4,-2)+1
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.
=EOMONTH(C4,-1)
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.
=DAY(EOMONTH(C4,0))
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.
=NETWORKDAYS(C5, EOMONTH(C5,0),$B$14:$B$20)
- NETWORKDAYS(C5, EOMONTH(C5,0),$B$14:$B$20) → returns the number of whole working days between two dates. Here, the C5 is the start_date argument, next, EOMONTH(C5,0) is the end_date argument, and the $B$14:$B$20 cells represent the optional holidays argument that is referenced from the “Holidays” table.
- Output → 19
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:
- 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.
=AVERAGEIFS(D5:D12,B5:B12,">="&B15,B5:B12,"<="&EOMONTH(B15,0),C5:C12,"RAM")
- AVERAGEIFS(D5:D12,B5:B12,”>=”&B15,B5:B12,”<=”&EOMONTH(B15,0),C5:C12,”RAM”) → finds average for the cells specified by a given set of conditions or criteria. Here, D5:D12 is the average_range argument which is the “Sales” column. Next, B5:B12 is the criteria_range1 argument which refers to the “Date” column and the “>=”&B15 is the criteria1 argument which is the value greater than and equal to the first day of “January”. Following this, B5:B12 is the criteria_range2 argument which refers to the “Date” column and the “<=”&EOMONTH(B15,0) is the criteria2 argument which represents the values less than and equal to the last day of “January”. Lastly, the C5:C12 is the criteria_range3 argument while the “RAM” is the criteria3 argument that indicates the “Sales” of “Ram”.
- Output → $4,214
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.
=COUNTIF(B5:B12,">"&B15)-COUNTIF(B5:B12,">"&EOMONTH(B15,0))
- COUNTIF(B5:B12,”>”&B15) → counts the number of cells within a range that meet the given condition. Here, the B5:B12 cells represent the range argument that refers to the Date column, while the “>”&B15 indicate the criteria argument that returns the count of the matched value.
- Output → 6
- COUNTIF(B5:B12,”>”&EOMONTH(B15,0)) → here, the B5:B12 cells represent the range argument that refers to the Date column, while the “>”&EOMONTH(B15,0) indicate the criteria argument that returns the count of the match.
- Output → 3
- COUNTIF(B5:B12,”>”&B15)-COUNTIF(B5:B12,”>”&EOMONTH(B15,0)) → becomes
- 6 – 3 → 3
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.
=SUMIFS($D$5:$D$12,$B$5:$B$12,">="&B15,$B$5:$B$12,"<="&EOMONTH(B15,0))
- 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, the $B$5:$B$12 represents the criteria_range_1 argument that points to the “Date” column, and the “>=”&B15 is the criteria_1 argument representing the values greater than and equal to the first day of “February”. Next, the $B$5:$B$12 is the criteria_range_2 argument which indicates the “Date” column and the “<=”&EOMONTH(B15,0) is the criteria_2 argument representing the values less than and equal to the last day of “February”.
- Output → $13,325
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.
=TODAY()
- Following this, enter the expression below into the D5 cell.
=AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),4))
- AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),4)) → checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE. Here, C5>EOMONTH(TODAY(),0) is the logical1 argument that checks if the date in the C5 cell is greater than today’s date, and C5<=EOMONTH(TODAY(),4) is the logical2 argument that checks whether the date in the C5 cell is less than the last day of the month 4 months in the future. Now, since the first argument is FALSE and the second argument is TRUE so the AND function returns the output FALSE.
- Output → FALSE
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.
=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 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.
=IF(B6-EOMONTH($C$14,0)<=30,D6,"")
- EOMONTH($C$14,0) → returns the serial number of the last day of the month before or after a specified number of months. Here, the C14 is the start_date argument, while 0 (zero) is the months argument representing the current month.
- Output → 12/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 equal to 30. If this difference is less than or equal to 30 then the function returns D6 (value_if_true argument) otherwise it returns “” (Blank) (value_if_false argument).
- Output → “” (Blank)
- Not long after, navigate to the F6 cell >> copy and paste the formula below.
=IF(AND(B6-EOMONTH($C$14,0)>=31,B6-EOMONTH($C$14,0)<=60),D6,"")
- 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. Now, since the first argument is TRUE and the second argument is FALSE so the AND function returns the output FALSE.
- Output → FALSE
- IF(AND(B6-EOMONTH($C$14,0)>=31,B6-EOMONTH($C$14,0)<=60),D6,””) → becomes
- IF(FALSE,D6,””) → Here, the FALSE value of the logical_test argument prompts the function to return “” (Blank) (value_if_false argument).
- Output → “” (Blank)
- Eventually, enter the following equation into the G6 cell.
=IF(B6-EOMONTH($C$14,0)>=61,D6,"")
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
Now, in the following section, we’ll explain the VBA code used to determine the end date of a future month.
- In the first portion, the sub-routine is given a name, here it is End_Date_of_future_month().
- Next, define the variables r_range and selected_cells variables and assign it as Range.
- Then, use For Loop to iterate through the cells in the r_range variable while applying the EoMonth function to return the end date and the Offset property to paste the results into the adjacent column.
- 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.
| Error | Occurrence |
|---|---|
| #NUM! | If the start_date argument is an invalid date. |
| #VALUE! | In case the start_date argument is text. |
- 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.
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. Lastly, visit ExcelDemy for many more articles like this.
