Generating a series of dates with the EOMONTH function

You will sometimes need to generate a series of dates in Excel. Most often you will need month-end dates, but no matter what kind of dates you need, the simplest way to generate them will include the EOMONTH function.

The EOMONTH function returns the last day of the month in relation to the start date.

 

The EOMONTH function syntax is as follows:

= EOMONTH ( start_date; number of months )

Start date is any given date, such as 31.12.2020.

Number of months is an integer number of whole months before or after start_date.

If that number is zero, the end of the start_date month date will be returned.

If that number is 1, the end of the month following the start_date month will be returned.

If that number is -1, the end of the month preceding the start_date month will be returned.

 

Given that, generating series of the end of month dates is easy:

generating a series of month-end dates

If we want to generate a series the start of month dates, we can do that by adding one more day to the month-end date of the previous month:

generating a series of moth-start dates

If we want to generate a series of dates containing every 10th day of the month, we can do that by adding 10 more days to the month-end date of the previous month:

generating a series of dates containing every 10th day of the month

If we want to generate a series of dates containing every 5th day from the end of the month, we can do that by removing 4 days from the month-end date:

generating a series of dates containing every 5th day from the end of the month

If we want to generate a series of dates containing every 10th of January, we can do that by manipulating the number of the months and adding 10 more days to the month-end date of the previous month:

generating a series of dates containing every 10th of January

 

Dig deeper:

Date and Time in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *