Fill a series of month-end dates

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 EOMONTHExcel Tips - Fill a series of month end dates 1

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-InsExcel Tips - Fill a series of month end dates 5

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… 🙂Excel Tips - Fill a series of month end dates 2

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:Excel Tips - Fill a series of month end dates 3 Excel Tips - Fill a series of month end dates 4

Easy!  Isn’t it?


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

2 Responses to Fill a series of month-end dates

  1. Michael Avidan says:

    EOMONTH can be used in, almost, any older version – just enable the Analysis Toolpak.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.