EOMONTH in Excel (Table of Contents)
EOMONTH in Excel
If you have learned MONTH function in excel understanding EOMONTH is not that 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.
EOMONTH Formula in Excel
The Formula for the EOMONTH Function in Excel is as follows:
The Formula of EOMONTH Function includes two mandatory parameters.
Start_date: “Starting date for the month”. It could be any day of the month not necessarily 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 EOMONTH Function in Excel?
This 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.
EOMONTH in Excel Example #1
Consider the below data and find the end of the month by using 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 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 below steps to know the tricks.
Step 1: Set up the table first
Step 2: First, find the next 3 months date by using EOMONTH.
It returns the result as 30/sep/2018.
Step 3: Apply a SUMIFS function to find the total units sold between 12-Jun-2018 to 30-Sep-2018.
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 looking 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.
Note: In the previous example I have used SUMIF but in this example, I am using SUMIFS.
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 EOMONTH function.
EOMONTH in Excel Example #3
We can get the start month also by using 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 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 has been a guide to EOMONTH in Excel. Here we discuss the EOMONTH Formula in Excel and how to use EOMONTH Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
