End of month excel formula

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

• come to this cell
• and lets say we want to make it quarterly, we put a 3 in there
• you’ll see these change to the end of the month, in 3 months time
• and you can even go semi annual or
• alternatively you can go backwards by putting a negative number in