Discover how to use EOMONTH function in a formula.
When to use
Dates are commonplace in models and usually run across one of the top rows in an Excel worksheet as part of a time series analysis: If it is necessary to have the month end date in each column, you cannot simply take the previous month’s date and add a constant to it, since the numbers of days in months vary. Fortunately, there is a function in Excel that will perform this calculation for us: EOMONTH(Specified_date,Number_of_months)
The “End of month” function calculates the end of the month as the Number_of_months after the Specified_date. For example:
- EOMONTH(31-Jul-20,0) = 31-Jul-20
- EOMONTH(3-Apr-05,2) = 30-Jun-05
- EOMONTH(29-Feb-08,-12) = 28-Feb-07
- The formula =IF(E$17=1,EOMONTH($D$14,0),EOMONTH(D16,1)) is explained below:
- An IF function is used which says: If the ‘Counter’ = 1 then return the last day of the month from the start date (cell D14), with 0 months added on =IF(E$17=1,EOMONTH($D$14,0),
- The second part of the formula says: If the ‘Counter’ is any number other than 1, then return the last day of the month from the previous period end (cell D16), plus 1 EOMONTH(D16,1))
Lesson notes are only available for subscribers.