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

Share

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

### Work with dates often?

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

### 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

Excel School made me great at work.
5/5

– Brenda

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.

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.

### Getting Started with Power BI – Sales Dashboard in one hour

Learn Power BI and create your own Sales Dashboard with this FREE Masterclass. Details inside.

## Related Tips

Excel Howtos

Excel Howtos

Excel Howtos

### Highlight due dates in Excel – Show items due, overdue and completed in different colors

Charts and Graphs

Excel Howtos

Excel Howtos

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

• Kevin says:

Exactly what I was looking for!
Thanks!

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

• Luis Ortiz says:

Perfect. This is exactly what I needed. Thanks Jason!

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

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.