Excel 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 2024-05-02.
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:
- TODAY Returns the date taken from your computer's clock and will return tomorrow's date when your computer's clock turns midnight. TODAY needs open and closed brackets — i.e. =TODAY() — but nothing between them.
- YEAR Returns, as a four-digit number, the year of the date that you supply to the function. So, =YEAR(TODAY()) returns the year of today's date. =YEAR("13-Feb-1959") returns 1959. And =YEAR(47301) returns 2029.
- MONTH Returns, as a number from one to 12, the month of the date that you supply to the function. So, =MONTH(TODAY()) returns the month number of today's date. =MONTH("13-Feb-1959") returns 2. And =MONTH(47301) returns 7.
- DATE DATE has three arguments — DATE(year,month,day) — each being an appropriate number. Give it these and DATE will return a whole number that represents that dates's position in Excel's built-in calendar —. =DATE(2020,2,13) will actually return 43874.
- EOMONTH EOMONTH has two arguments — EOMONTH(date,months). You give EOMONTH a date as a starting point and a number for the months in the future, or the past, for which you need the month-end date. Imagine today's date is February 13th 2020.
=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:
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) —
Thanks, in anticipation.
Disclaimer
David Wallis 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.