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

**Here is a handy trick to calculate last day of any month.**

Assuming ** y **and

**contain the year & month for which you want to find the last day’s date, write**

*m*=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:

- Using Date & Time in Excel
- 42 useful Date & Time formulas
- How to calculate common holiday dates in Excel?
- How to calculate payroll dates?
- How to sort a bunch of birth dates by
*birthday*? - Check if two dates are in same month

### Sign-up for our FREE Excel tips 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

### Leave a Reply

Secret Agent KV’s Chops…what’s in HIS Personal Macro Workbook? |
Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age |

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

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

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

Love it

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.

Exactly what I was looking for!

Thanks!

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.

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

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

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

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

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

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

@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″)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….

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.