dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Excel Formula for Last Day of a Future Month

Last updated on 2018-08-01 by David Wallis.


General Formula

These formulae should work in all versions of Excel.

This one will calculate the last day of the current month:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

This, the last day of next month:

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1

And this, the last day of the month after next:

=DATE(YEAR(TODAY()),MONTH(TODAY())+3,1)-1

If you haven’t aleady done so, you need to apply a date format to the cells containing your formulae.


Using the EOMONTH Function

If your version of Excel provides the EOMONTH function, then use it like this for the end of this month:

=EOMONTH(TODAY(),0)

Like this for the end of next month:

=EOMONTH(TODAY(),1)

And like this for the end of the month after next:

=EOMONTH(TODAY(),2)

If you haven’t aleady done so, you need to apply a date format to the cells containing your formulae.


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.

“Excel can’t calculate the end of the world; but it can the end of the month.”

Anonymous