How to Get End of the Month Date in Excel

Last Updated: May 09, 2024
puneet-gogia-excel-champs

- Written by Puneet

Let’s say you’re managing a team and must submit monthly performance reports by the last day of each month. In Excel, you can use a formula to automatically find out the last day of any given month, ensuring you always know the deadline.

The best way to calculate the last date of the month is by using the EOMONTH Function. But apart from this, you can also use other methods formulas.

Using a formula or any other dynamic method has the benefit that we don’t need to calculate it again and again. Today, in this tutorial, I’d like to share with you 3 simple and dynamic ways to get the end of the month date in Excel.

Get the Last Day of the Month using EOMONTH Function

EOMONTH is my favorite function to calculate the last day of a month. This function is specifically designed for these kinds of calculations.

All you have to do is just specify a date current date, and the number of months you want to calculate the last date before and after the start date. Here is the syntax:

=EOMONTH(start_date,months)

In this syntax, the start date will be the current date and months will be a number that represents the number of months forward or backward.

End Date of the Same Month

Let’s say, you have the date “06-Feb-2017” and you want to get the last day of “Feb”. In this case, the formulas you need to use are:

=EOMONTH("22-Feb-2017",0)
get end of day for a present month using eomonth

In the above example, when you use EOMONTH, has returned 28-Feb-2017 which is the last date of the Feb month.

How does it work: Here, you have used “0” for the number argument because the start date you have used is of Feb month and you won’t have the last day of the same month. When you use “0” it will simply return the last date for the same month for which you have the start date.

To create a dynamic formula you can use the TODAY Function in the start_date argument.

End Date of the Future Month

Now, let’s say, from the same date you want to get the last day of “Apr”. In this case, the formulas you need to use are:

=EOMONTH("22-Feb-2017",2)
get end of the month date for future month using eomonth

In the above example, the formula has returned 30-Apr-2017 which is the last date of the Apr month.

How does it work

Here, you have used “2” for the number argument and the start date you have used is of Feb month. Now, if you look, Apr is exactly 2 months after Feb so that’s why it has returned 30-Apr-2017.

End Date of the Previous Month

And, if you want to get the last date of a previous month you can use a negative number for that. Let’s say you want the last day of Jan. So, the formula you need to use:

=EOMONTH("22-Feb-2017",-1)
get end of the month date for previous month using eomonth

In the above example, the formula has returned 31-Jan-2017 which is the last date of the Jan month.

This formula finds the last day of the month. In our example, you input “22-Feb-2017,” and by using -1, you tell Excel to go backward for one month. In the result, it will show you the last day of January 2017, which is January 31, 2017.

DATE Function to Get Month’s Last Date

DATE Function is another approach to getting the last day in excel. The date function helps you to create a valid excel date by specifying the year, month, and day. Here is the syntax:

=DATE(year,month,day)

As I have mentioned, in the date function, you need to enter the year, month, and day, and it returns a valid date according to that. Now, there is a small trick that you can do with day argument.

If you use 0 as a day it will return the last date of the previous month. For example, if you specify, Year: 2017, Month: 4 (that’s May) and Day: 0 it will return 30-Apr-2017 in the result.

And, if you want to calculate the last day of the current month the formula will be:

get end of the month date using date function
=DATE(CurrentYear,CurrentMonth + 1,0)

Or, if you just have a date to refer then the formula will be:

=DATE(YEAR(date),MONTH(date)+1,0)
=DATE(YEAR(A1), MONTH(A1) + 1, 0)

It takes the year and month from the date in A1, adds 1 to the month, and converts it into the next month’s date. Using the day to 0 tells Excel to return to the previous month’s last day.

Get Last Day with a UDF

A great VBA Example. This is a macro code for using a VBA UDF to get the last day of the month. You just need to copy it into your VBA editor.

Function LastD(Optional sd As Date = 0) As Date
If sd = 0 Then 
sd = VBA.DateLastD = VBA.DateSerial(VBA.Year(sd), VBA.Month(sd) + 1, 0)
End Function
get end of the month date using vba

In your worksheet, type “=LastD(” and insert the date for the month or refer to a cell. And, if you want the last day of the current month then simply left it blank using brackets.

Wrap Up

As I said, the month’s last day can be helpful for you while calculating target dates, due dates, or completion dates. And, now we have three different ways to get it.

If you use EOMONTH then it’s really easy to create a dynamic formula that can help you to date for past or future months and I think it’s the best way to do it. I hope you found it useful and it will help you take your skills to the whole next level.

Now tell me one thing. Which one is your favorite method above all? Or, do you have a different method? Please share with me in the comment box, I would love to hear from you. And don’t forget to share it with your friends.

13 thoughts on “How to Get End of the Month Date in Excel”

  1. please help me

    if today 2nd day of current month is monday and 1st date of current month is Sunday then return last month end date in excel.

    Example if today 02-05-2022 is Monday then return last month end date (30-04-2022)

    please help me

    Reply
  2. Back in the Olde Days. . . .
    Cell b:3 has date of 01/31/2020
    Cell b:4 =date(year(b3+35),Month(b3+35),1)-1
    The constant of 35 is any number greater than 31 and less than 31+28

    Reply
  3. Grate tip , thank you

    I have a question.

    How can I get the count of days of current month excluding sundays.??

    Reply
    • Hey Eduardo.

      get the count of days of current month excluding sundays.

      =networkdays.intl(eomonth(today(),-1)+1,today(),11)

      Reply

Leave a Comment