End of Month Excel formula

EOMONTH which is the end of month excel formula, generates the date of the last day of the required month, based on an input start date.

In this segment, you will learn how to find, activate and use these functions. However it is very important to point out, that these functions are only available when you have your Analysis Toolpack loaded.

You can visit our site to see how to load the Analysis Toolpack

In this example we have an income statement and what we’d like to do is we are going to have actual numbers dated the 15th January, and what we’d like to do is put in some forecast numbers, but we’d like this to calculate the correct dates.
We can make use of the EDATE feature, in order to activate it

you click in the cell you want the formula to go in

you click on the Function Wizard

and then you find the Date and Time section,

and you click on EDATE

and you say ok

and this dialogue box appears

The first option, it asks you for the cell that represents the start date, in this case you would click on this cell,

the second option asks you the number of months before or after the reference date, in this case we’ve set up a cell to indicate it, so we can click on it

and because this needs to be a constant reference to this cell, we need to make this absolute values by putting the dollar signs in,

and we say ok

and you’ll see it generates a month after that,

and we can copy that across,

And now if we want to we can

click in this cell

and for example, say make it 2 months across,

push enter and you’ll see that works pretty well.

If you would like the forecast dates to make reference to the start date, but to show the date at the end of the month, you should rather make use of the EOMONTH feature. In order to activate it,

you click on the cell where you want the formula,

you activate the Function Wizard,

you’ll go to the Date and Time section, and you find the EOMONTH formula

you’ll click on it

and you’ll say ok,

this dialogue box will appear,

The first option is to give it the start date or the reference date, in this case is this cell here,

second option asks you how many months before or after the start date should this date be, and we are going to click on our cell here

and because we want it to consistently reference between the cells, we need to make it absolute referencing,

when you push ok,

what you’ll get is the end of the next month

and we can now copy

and past this along

and you’ll see that each cell represents the end of the next month

What you have now is automatically generated dates, so what you can do is