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

Posted on November 21st, 2013 in Excel Howtos - 11 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:

Your email address is safe with us. Our policies

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

11 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] | – Learn Microsoft E… […]

  7. Jason M says:

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




  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.


Leave a Reply