Excel EOMONTH Function (Table of Contents)
EOMONTH Function in Excel
Like the other date functions in excel, EOMONTH formula is a great function which gives us the value for the last date. It can also provide us with the start date in excel. There are many uses for this function. Like for example, this function is nested with SUMIFs function to find out the sum of the months.
The syntax for EOMONTH in excel is as follows:
Now let us discuss the arguments required in this function. Both the arguments given to the EOMONTH function is mandatory as they are necessary to evaluate the value.
- Start Date: Start Date argument is the starting date we provide to it and it needs to be in date format.
- Months: This parameter is actually the number of months given to the function.
Examples of EOMONTH Function in Excel
Now let us understand this function with few examples to understand the basics of the function. We will use this function in three examples. One to calculate the last day of the month and second to calculate the first day of the month and the third will be, to sum up the number of months based on value.
EOMONTH Function – Example #1
We will use the EOMONTH function to calculate the last date of the month. Consider the below data,
In A column we have the date value while in B column we have the months value. Let us see what this function does to this data.
- In cell C2 write the following formula,
- For start date select cell A2,
- Now for months select the data in Months column,
- Close the parenthesis and press enter to see the result,
- Drag the formula to cell C12 to see the result for the rest of the data,
What this function does that it adds the value given in months into the date provided and returns the last date of the month. For example in the first data one is added to march which gives us April and the result is the last date of the April month in that year.
EOMONTH Function – Example #2
Now we will use this function to find the first date of the given month. Let us use the same data from example 1 and we will find the first date from it. Consider the following data,
The above data is the same output data from the first example.
- In Cell B2, write the following excel formula,
- Select Cell A2 for the reference for the start date of the month,
- Now write -1 as the month reference and close the parenthesis and write +1.
- Press Enter to see the result.
- Drag the formula to cell B12 for the result of the rest of the data,
Now what this function did is it did subtracted -1 from the month but added one to it and gave the first date as the result.
EOMONTH Function – Example #3
Now in this example, we will use some realistic example, Consider the following data
We have sales data for various products in the given dates. For example on the 15th of Jan product A had sales done of 10000 rs. And so on for the other products. Now let us try to find the total sales done in any given month.
- In cell Adjacent to January write the following formula,
- Press Enter to see the result.
- Now drag the formula to March data and see the result.
Now let us understand how this function works, The sumifs function takes the sum range as input first which is the sales data then criteria range which is the date range and the criteria is that the month should be January.
Similarly, we calculate the value for the respective Feb and March.
Explanation of EOMONTH Function in Excel
As explained EOMONTH function is used to find out the first or the last date in excel. There can be many practical uses for this function. For example like in the first data we have calculated the last date of the data and when we change the format to the long date we can see the Day exact day name like Tuesday or Monday which will be the last day for the given data.
Things to Remember
There are few things we need to remember about EOMONTH Formula In Excel:
- If the start date is not in date format the function reports an error as a result.
- The month’s parameter can be positive or negative.
- This function can be nested with other functions for different uses.
You can download this EOMONTH Formula Excel Template from here – EOMONTH Formula Excel Template
This has been a guide to EOMONTH. Here we discuss how to use EOMONTH function in Excel and its formula along with a practical example and downloadable templates. You may also look at these useful functions in excel –