Sometimes we need to get an end of the month date to use as a completion date, due date, as a target date or sometimes we need it to calculate the days between two dates.

Without any efforts, we can easily add it manually, but there are some formulas which can help us to calculate it.

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

Using a formula or any other dynamic method has a benefit that we don’t need calculate it again and again.

So today, in this post, I’d like share with you 3 simple and dynamic ways to get the end of the month date in Excel.

So let’s get started and please download this same file from here to follow along.

Methods To Get End of the Month Date

This is the truth, rest all is lie.

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

All you have to do, just specify a date current date and number of the 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 a current date and months will be a number which represents the number of months forward or backward.

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

=EOMONTH(“22-Feb-2017”,0)

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 works

Here, you have used “0” for number argument because the start date you have used is of Feb month and you want 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.

Expert Tip==> To create a dynamic formula you can use TODAY Function in the start_date argument.

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:

=EOMONTH(“22-Feb-2017”,2)

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

how does it works

Here, you have used “2” for number argument and the start date you have used is of Feb month.

Now, if you look, Apr is exactly 2 months after the Feb so that’s why it has returned 30-Apr-2017.

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 last day of Jan.

So, the formula you need to use:

=EOMONTH(“22-Feb-2017”,-1)

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

how does it works

Here, you have used “-1” for number argument and Jan is exactly 1 month before the Feb.

a simple way for minimalists.

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

Here is the syntax:

DATE(year,month,day)

As I have mentioned, in 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 which you can do with day argument.

Here's the trick:

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+1 (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:

=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)

This is a macro code for a 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.Date

LastD = VBA.DateSerial(VBA.Year(sd), VBA.Month(sd) + 1, 0)

End Function

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

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

If you use EOMONTH then it’s really easy to create a dynamic formula which can help you to a 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 whole next level.

Now tell me one thing. Which one is your favorite method from above all? Or, 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.

- Vikas G
- Puneet Gogia

- Dilshad Ahmad
- Puneet Gogia