You will sometimes need to calculate one date based on another date. Most often, you will need the first or last date of the current month, the first or last date of the current year, the previous or future month, year, quarter dates, etc. No matter what kind of dates you need, the simplest way to generate them will include the EOMONTH function.
The EOMONTH function returns the last day of the month in relation to the start date.
The EOMONTH function syntax is as follows:
= EOMONTH ( start_date; number_of_months )
The start date can be any date, such as January 1, 2020.
The number of months is an integer number of whole months before or after the start_date.
If that number is zero, the end of the start_date month will be returned.
If that number is 1, the end of the month following the start_date month will be returned.
If that number is -1, the end of the month preceding the start_date month will be returned.
The typical application of the EOMONTH function looks something like this – we are calculating month-end dates from our starting dates:
Month-end dates in column B are calculated based on the dates in column A, with the number_of_months part of the argument being zero.
By using the number_of_months part of the argument, we can also go back or forward in time and calculate any desired month.
By adding or subtracting days from those month-end dates, we can calculate any desired date in the month. No matter how dates are formatted, the underlying values saved in cells are still decimal numbers. Given that, those additions and subtractions can be done by simple arithmetic and/or basic functions.
Understanding those concepts, in the following example, we will calculate the first of the month dates:
The first of the month dates in column B are calculated based on the dates in column A, with the number_of_months part of the argument being minus one. Due to that minus one, the EOMONTH function is now returning the previous month-end date, and by adding one day to that date, we are returning our first of the month dates.
Note that other specialized functions, such as the EDATE function, which is used for retrieving the same date in the previous or subsequent months, do exist. However, the unique qualities of the EOMONTH function described here make it the go-to tool for calculations of any given future or past dates from the starting date.
Consider this example, Report Preface for daily reporting:
Using the EOMONTH function, we can set up formulas that will calculate the needed dates no matter what the reporting date is:
Consider the formula that is used to calculate the end of the quarter date.
First, we are deducting the number of months (current reporting date).
Next, we divide the number of months (current reporting date) by 3 (as there are 4 quarters containing 3 months each) and round that number up.
Next, we multiply that rounded number (2 for the second quarter) with the number of months in the quarter (3). We’ve now calculated that we need to retrieve the end of the 6th month of the year, as that is the end of the second quarter.
Finally, we are retrieving the desired date as the end of the current month, minus 4 months, plus 6 months.
We were primarily using the MONTH function in our number_of_months part of the argument, but other basic Excel Date and Time functions can be used as well, whether to calculate a certain month-end date, whether to subtract or add a certain number of days to that date.
Some of them are shown in the following example:
Also, consider the formula that is used to calculate the week of the month for the current date.
First, we calculate the current week of the year with the help of the WEEKNUM function.
Next, we subtract the number of weeks of the year for the first day of the current month (retrieved with the help of the EOMONTH function).
Finally, we add one week to our number of weeks – we want the week that contains the first of the month included.
Dig deeper:
2 thoughts on “EOMONTH function: calculate past or future date from the starting date”