DMW logo
tel 01732 833085
tonbridge · kent · UK

Formulas for Dates of First and Last Days in This and Future Months

How to use Excel’s DATE, YEAR, TODAY, MONTH and EOMONTH functions to calculate the dates of the first and last days in this and future months.

Last updated on 2020-05-26 by David Wallis.


Date of Last Day in Current Month

This formula should work in all versions of Excel and responds to the date showing on your computer's clock

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

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

=EOMONTH(TODAY(),0)

Explaining the individual functions in the compound formulas:

=EOMONTH("13-Feb-2020",0) returns February 29th 2020, Excel correctly recognising 2020 as a leap year. The zero indicates you’re not moving the month of the date supplied to the function.


Date of Last Day in Future Months

These formulas will return the last day of next month:

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

=EOMONTH(TODAY(),1)

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

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

=EOMONTH(TODAY(),2)

The digit in blue is the variable in each formula that pushes the result into a future month.

Here are some other examples of EOMONTH at work:

=EOMONTH("13-Feb-2020",-1) takes you back a month to the end of January 2020.

=EOMONTH("13-Feb-2020",-12) takes you back 12 months to the end of February 2019, Excel correctly recognising 2019 is not a leap year.

=EOMONTH("13/02/2020",12) takes you on 12 months to the end of February 2021, Excel correctly recognising 2021 is not a leap year.

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


Excel’s Built-in Calendar

Excel stores a date as a whole number based on a calendar that starts with day one on January 1st 1900. Input the digit 1 into a cell and apply the Short Date format to confirm this.

Hence 43874 represents February 13th 2020, that is 43874 days on from January 1st 1900.

When you are doing calculactions on dates you’re effectively doing arithmetic with numbers. For example, to calculate the number of days between two dates, simply subtract one from the other:

Date difference demo


Your Support for dmw TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification


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