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