EOMONTH in Excel (Table of Contents)
EOMONTH in Excel
If you have learned MONTH function in excel understanding EOMONTH is not a difficult task to get inside.
In EOMONTH EO stands for “End of the”
EOMONTH = End of the Month
MONTH function in excel extracts the month from the supplied date. EOMONTH will return the end of the month for the supplied date.
For example: =EOMONTH (16-11-2018, 0) would return 30-11-2018.
=EOMONTH (16-11-2018, 1) would return 31-12-2018.
Syntax for EOMONTH Formula
The Formula for the EOMONTH Function in Excel is as follows:
Argument of EOMONTH Formula:
The Argument of 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.
4.8 (2,146 ratings)
View Course
EOMONTH in Excel – Example #1
Consider the below data and find the end of the month by using the months column.
- Apply 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 is going back for 5 months and returned the value as 30-04-2017.
EOMONTH in Excel – 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 after 12-Jun-2018 until the end of the next 3 months. 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. For the second criteria range, we are giving the criteria as “Less than or equal to 30-Sep-2018”.
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 Units Sold column. This is the first criteria range and the criteria range is 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 Date column. This is the criteria for criteria range 2. i.e. less than equal 30-Sep-2018. This date is obtained by the EOMONTH function.
EOMONTH in Excel – 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 About EOMONTH Function in Excel
- 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 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 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 downloadable excel template. You may also look at our other related articles to learn more –
