return end of month and same date in future or past months

If you need to know the end of month dates for a list of months you could simply type in these date if you need just a few in a list.

But what if you need to enter a long list of these dates and as I do, need to recite ‘30 days in September, April, June & November…..etc‘ for each month.  This could take a bit longer then necessary!

Excel provides a function to automatically return the end of month date based on an initial date.

The example below takes the initial date of 15 Apr 15 and returns an end of month date either in the past or future.

The function is built as follows ‘=EOMONTH(Intital_date, Number_of_months)’

EOMONTH

To return a list of end of month dates without having to manually create the function with Number_of_months value in the function or to have another column of sequential numbers, you could enter the following function into cell E6 based on the example above ‘=EOMONTH(E5+1, 0)’.  This takes the date in cell E5 (30 Apr), adds one day (to make 1 May) and the function then returns the end of month date for 1 May.  You can then drag or copy this function down to create the list of dates required.

 

If you wanted to return the same date in future or past months could simply enter these manually if you just need a few, but if you need a lot you could use the EDATE function to do all the work for you.

The example below takes the initial date of 15 Apr and returns the same date in past or future months.

The function is built as follows ‘=EDATE(Initial_Date, Number_of_months)

EDATE

To return a list of dates without having to manually create the function with Number_of_months value in the function or to have another column of sequential numbers, you could enter the following function in cell E23 based on the example above ‘=EDATE(E22, 1)’.  This takes the date in cell E22 (15 Jan) and the function then returns the same date in the following month.  You can then drag or copy this function down to create the list of dates required.

Get more great content from Rousseau Associates

Start your project with us today

Call +44 (0)1757 269461

Discuss your requirements today