Updated June 9, 2023
EOMONTH in Excel
The EOMONTH in Excel is used to get the end date of any month from the selected dates. Here we just need to select the start date from which the month’s end date is to be calculated and then select the month’s sequence out of 12, for which we need to find the end date. For example, if we have selected today’s date as the start date and months as 2, the end month date will be the last of the following 2 month’s end dates.
The syntax for EOMONTH Formula:
The Formula for the EOMONTH Function in Excel is as follows:
The argument of the EOMONTH Formula:
The Argument of the EOMONTH Function includes two mandatory parameters.
- Start_date: “Starting date for the month”. It could be any day of the month, not necessarily the first day.
- Months: The number of months before or after the start date.
The result will be in the form of serial numbers. We need to apply the date format to look perfect.
How to Use the EOMONTH Function in Excel?
EOMONTH Function is very simple and easy to use. Let us now see how to use the EOMONTH Function in Excel with the help of some examples.
Example #1
Consider the data below and find the month’s end using the month’s column.
- Apply the EOMONTH formula to get the end-of-the-month results.
- The end-of-the-month results are given below:
- For the first date, I gave 5 as the month; the result is 31-10-2016. From 01-05-2016, I am going ahead for 5 months. Here formula excludes the current month and takes the next five months.
- If you look at the negative number, I have given -5; the result is 30-04-2017. It was going back for 5 months and returned the value as 30-04-2017.
Example #2
We can use the EOMONTH function along with other functions too. Consider the below data for our example.
From the above data, we need to calculate the total units sold from 12-Jun-2018 until the end of the next 3 months. Follow the below steps to learn the tricks.
- Set up the table first.
- Find the next 3 months’ dates by using EOMONTH.
- It returns the result as of 30/Sep/2018.
- Apply a SUMIFS function to find the total units sold between 12-Jun-2018 to 30-Sep-2018. The formula used here is =SUMIFS ($D$2:$D$16, $C$2:$C$16,”>=”&F2, $C$2:$C$16,”<=”&G2)
- It returns the result as 15,645.
=SUMIFS ($D$2:$D$16, $C$2:$C$16,”>=”&F2, $C$2:$C$16,”<=”&G2)
This section of the formula looks for which column to SUM, i.e., the Units Sold column. This section looks at the date range as criteria range1. We are giving our criteria as Greater than or equal to 12-Jun-2018 for the criteria range. This section looks at the date range as a criteria range2. We are giving the criteria as “Less than or equal to 30-Sep-2018” for the second criteria range.
Alternative Method for Example #2:
There is an alternative method, for example, 2. However, use the EOMONTH inside the SUMIFS function. Apply the below formula to get the total units sold.
The formula is =SUMIFS ($D$2:$D$16, $C$2:$C$16,”>=”&F6, $C$2:$C$16,”<=”&EOMONTH (F6, 3))
The first part looks at the sum range, the Units Sold column. This is the first criteria range, and the criteria range is the Date column. It is the criteria for criteria range 1. i.e., greater than equal 12-Jun-2018. This is the second criteria range, and the criteria range is the Date column. This is the criteria for criteria range 2. i.e., less than equal to 30-Sep-2018. The EOMONTH function obtains this date.
Example #3
We can get the start month also by using the EOMONTH function. Consider the below example.
- Apply the below formula to get the start of the month.
- The result of the start of the month is 5/1/2016.
- Drag the Formula to get the rest of the results:
Things to Remember
- Starting date could be any day of the month, not necessarily the first day of the month.
- We can supply positive and negative numbers for the month’s parameter.
- If you need the same month end of month date, give 0 for the month’s argument; if you need next month, give 1; if you need after 3 months, give 3, etc…
- The date range should be greater than 01st Jan 1900. If anything is before that date, the result will be #VALUE!
- A month should always be a numerical value.
- By using EOMONTH, we can also get the start of the month date. Refer to example 3.
Recommended Articles
Although this is a guide to EOMONTH in Excel. Here we discuss How to use the EOMONTH Formula in Excel, Excel examples, and a downloadable Excel template. You may also look at our other related articles to learn more –
