fbpx
Search
Close this search box.

Find last day of any month with this simple trick [formulas]

Share

Facebook
Twitter
LinkedIn

last-date-of-month-formula in ExcelHere is a handy trick to calculate last day of any month.

Assuming y and m contain the year & month for which you want to find the last day’s date, write

=DATE(y, m+1,0)

That is right, you can use ZERO (0) as the day.

When you do this, Excel tells us the last day of previous month.

How to calculate last day if I have a date in that month:

Instead of year and month, if you know a date & you want to find what is the last date… then use:

=EOMONTH(date, 0)

How to calculate the last working day of a month?

If you just care about working days.. then use:

=WORKDAY(DATE(y,m+1,1), -1)

This will give us the last working day of a month.

Dealing with holidays:

Very simple. Pass on a list of holidays to WORKDAY as last parameter. Like this:

=WORKDAY(DATE(y,m+1,1), -1, F1:F20)

This assumes, F1:F20 has a list of holiday dates.

Dealing with different weekend types:

Not everyone observes Saturday & Sunday as weekend. For example, I do not work on Tuesdays. You might have Sunday thru Thursday work week. If so,

use WORKDAY.INTL function, like this:

=WORKDAY.INTL(DATE(y,m+1,1), -1, 7)

Weekend type 7 is for Friday & Saturday weekend.

How do you calculate last date of a month?

Do you use some other formula to do this? Please share your tip using comments.

Work with dates often?

If you work with dates, then spend some time reading these tips:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

16 Responses to “Find last day of any month with this simple trick [formulas]”

  1. Stelios says:

    Isn't EOMONTH giving the last day of any month? Why to prefer this tip? EOmonth can also be combined with date.

  2. Rudra says:

    Useful tip chandoo.
    I used =DATE(2013,11,0) and still got 10/31/2013. Then why do we need m + 1?
    I also found that if you replace 0 by -1(day),it automatically decreases date by one day i.e(10/30/2013) . WoWWWW!!!!

  3. Dustin says:

    Love it

  4. Heather R says:

    I use =EOMONTH(TODAY(),-1) to give me the last day of the prior month - it's useful for some of my automated reports where I am running the report after the first of the month to account for the previous month's sales.

  5. Stephen says:

    I find that when you have the month in a table as a number, this trick works well, but if you have the actual date to go from, EOMONTH is a lot easier.

  6. […] Find last day of any month with this simple trick [formulas] | Chandoo.org – Learn Microsoft E… […]

  7. Jason M says:

    Roundabout way of getting last day of the month from a date in the month:

    =EDATE(TEXT(A1,"m/\1/y"),1)-1

    or

    =EDATE(A1-DAY(A1)+1,1)-1

  8. Kiev says:

    Nice trick, just wonder how did you find it???

  9. […] Find last day of any month with this simple trick […]

  10. Cherry says:

    Hello! May I ask help on this?

    I used the following inputs for the date function WORKDAY.INTL:

    Start date: 12/3/2014
    Days: 6
    Weekends: Mon, Tue, Thu, Sat, Sun
    Holidays: none

    The syntax appeared as =WORKDAY.INTL("12/3/2014",6,"1101011")
    The answer I got was 12/24/2014 when it was supposed to be 12/19/2014.

    Could this be a bug?

    Will greatly appreciate any help on this.

    Thank you very much.

    Cherry

    • Hui... says:

      @Cherry
      I think that the start date isn't counted as a day
      That is if you use:
      =WORKDAY.INTL(DATE(2014,12,3),1,"1101011")
      =12/5/2014
      Hence your confusion as to the answer
      If you want to include the start date then you will need to use
      =WORKDAY.INTL("12/3/2014",6-1,"1101011")

  11. Tammyw says:

    HI, I have the same issue. i'm testing a date (date + 5 workdays) and if that date is a friday then i need to -1 day. but if i put -1 in the 'days' section of the formula it does every day -1 not just when the result date falls on a friday, saturday or sunday.
    Need some expert help....
    This is what i have and the result is a Friday?
    =WORKDAY.INTL(workday(date(2014,08,1),1,Holidays),0,"0000111")
    Result = 01/08/2104 (1st August 2014) and it should be 04/08/2014 (4th August 2014) because the 1st in a Friday.
    Any help appreciated....

    • Tammyw says:

      Sorry, edit to earlier comment;
      testing date (date + 1 workday) and if i put a +1 or -1, it is applied to all dates not just when Friday, Saturday and Sunday falls.

  12. Nathan says:

    I am really impressed with this formula. It seems to be doing everything I want correctly. The only thing it doesn't do is take into account leap years when working out the last working day of the month. i.e for February 2016 it works out the last working day as Friday 26th and not Monday 29th. Other than that it is perfect.

Leave a Reply