Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

Get Last Day of a Month

    how to get last day of a month

    Sometimes you need to use the last day of a month in a calculation as a completion date, due date, or as a target date. You can easily add a month’s last date manually but there are some formulas which can help you calculate it.

    And, the best way to calculate it by using EOMONTH Function. But apart from this, you can also use other methods (VBA).

    Today, in this post, you will learn three simple methods to get last day of a month.

    So, let’s get started.

    Table of Content

    1. EOMONTH Function
    2. DATE Function
    3. Using UDF

    Download this sample data file from here to follow along.

    1. Get Last Day of the Month With EOMONTH Function

    EOMONTH is my favorite function to calculate the last day of the month. This function is specifically designed to get the last date of a given month.

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

    #1 Last Day of the Same Month

    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)

    get last 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 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.

    Quick Tip: To create a dynamic formula which will calculate last day when ever month changes you can use TODAY Function in the start_date argument.

    #2 Last Day 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:

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

    get last day 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 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.

    #3 Last Day of the Previous Month

    And, if you want to get the last date of a previous months 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)

    get last day 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.

    How does it work?

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

    2. Use DATE Function to Get Last Day

    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 for this function.

    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 is 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.

    get last day of at month using date

    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)

    3. Get Last Day with a UDF

    Here I have a VBA code as a simple UDF function to get the last day is a much smarter way. The credit goes to extend office for the inspiration of this code.

    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

    get last day of a month using vba

    How to use this code?

    First of all, you need to add this code in your VBE by inserting a new module. After that, 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 the it blank using brackets.

    Try this: 50+ Useful Macros Codes

    Sample File

    download sample excel file

    Conclusion

    As I said, month’s last date can be helpful for you while calculating target dates, due dates, or completion dates. And, now you have three different way to calculate it. If you want to use a dynamic date while calculation you can use TODAY function.

    I hope you found this formula tip useful.

    If you ask me, EOMONTH is my favorite one, because that function is specifically for this calculation. But, the UDF code I have shared with you is more convenient in usage. And, in that UDF I have used DATE function method.

    And now, you have to tell me one thing. Which methods is your favorite one? And, if you have any other method then you can share with me in the comment box.

    I would love to hear from you.