In many cases, we may want to create a list of month end dates.  This can be done either by formula or Fill.  Either way, we should input the first month end date (starting date) manually.

Using EOMONTH

Syntax: EOMONTH(start_date, months)

EOMONTH simply means end of month.  Start_date represents any date to tell Excel where you start.  Months indicates the number of month(s) before or after the Start_date, i.e. -10 means 10 months before; 0 means same month of ; +1 means one month after start_date.

Note: This function applies to Excel 2010 or later.

EDIT:  Thank Michael for the correction.  If you are using earlier version of Excel, you may need to activate the Analysis ToolPak Add-Ins first.  To do so, go to Tools–> Add-Ins

If you have no luck in using this function, try the alternative below.

Using Fill Months

The screenshot below describes every thing you need to do… 🙂

However, if you want a long list that extends to say 31/12/2020, you may attempt to drag your list all the way down (if you have time)…  Simply tell Excel to do so.  To do that, select the cell with the starting date, then go to

HOME tab –> Editing group –> Fill –> Series and then follow the screenshot:

Easy!  Isn’t it?