## About this lesson

Illustrations and issues with the EOMONTH function.

## Exercise files

Download this lesson’s related exercise files.

EOMONTH.xlsx12.3 KB EOMONTH - Solution.xlsx

12.5 KB

## Quick reference

### EOMONTH

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)**

### Instructions

#### Overview

Syntax: **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

#### Example

- 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.