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

Posted on November 21st, 2013 in Excel Howtos - 15 comments

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:


Sign-up for our FREE Excel tips newsletter:

Free Excel tips book - joining bonus - Chandoo.org newsletter

Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

Leave a Reply