EOMONTH in Excel (Table of Contents)
EOMONTH in Excel
Eomonth function 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 end date of the month to be calculated and then select the sequence of the month out of 12, for which we need to find the end date. For example, if we have select today’s date as the start date and months as 2, then the end month date will be the last date of the following 2 month’s end date.
The syntax for EOMONTH Formula:
The Formula for the EOMONTH Function in Excel is as follows:
The argument of 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 of the month.
- 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 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 below data and find the end of the month by using the month’s column.
- Apply the EOMONTH formula to get the end of the month results.
- The end of the month results is given below:
- In the first date, I have given 5 as the month, and 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, and 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.
We need to calculate the total units sold after 12-Jun-2018 until the end of the next 3 months from the above data. Follow the below steps to know the tricks.
- Set up the table first.
- Find the next 3 months date by using EOMONTH.
- It returns the result as 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 looking for which column to SUM, i.e. Units Sold column. This section looks at the date range as criteria range1. For the criteria range, we are giving our criteria as Greater than or equal to 12-Jun-2018. This section looking at the date range as 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. We can 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 is looking at the sum range, and the sum range is the Units Sold column. This is the first criteria range, and the criteria range is the Date column. This 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 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 as well as negative numbers for the month’s parameter.
- If you need the same month end of the month date, give 0 for month’s argument, if you need next month give 1, if you need after 3 months then give 3 etc…
- The date range should be greater than 01st Jan 1900. If anything prior to that date, then 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
This is a guide to EOMONTH in Excel. Here we discuss the How to use the EOMONTH Formula in Excel along with excel examples and a downloadable excel template. You may also look at our other related articles to learn more –
