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

Leave a Reply
Secret Agent KV’s Chops…what’s in HIS Personal Macro Workbook?  Formula ForensicsNo. 036: Calculating Costs that Vary by Year and Age 
10 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.
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(A1DAY(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 […]