MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return End of Month Date

December 13, 2001 - by Bill Jelen

Tony wrote with a question that looks simple, but requires a fairly complicated formula:

I'm running excel 97 and I want some of the date formated cells in column 'K' to return a date that is the end of the month when I enter any day of that month in 'G'. I've tried EMONTH from a early version of excel but have not got it to work in this version.

There are a few ways to solve many problems in Excel. I am sure someone will come up with something simpler, but at the moment, this formula comes to mind:


The DATE() function generally accepts three arguments: A year, a month, and a day. So, December 13 2001 could be entered as =DATE(2001,12,13).

The date function is also fairly forgiving. If you specify an invalid number of months, such as 13, it will give the correct date. =DATE(2001,13,1) will be January 1, 2002. This makes it a great function when you need to add or subtract to the current date.

In solving this problem, it is easier to find the first day of the next month and then subtract one day. This is easier, because we know the day portion of the first of next month is always 1. Whereas, if you tried to find the last day of this month, you would have to check for leap years, etc - it would generally be a mess.

If you have a date in E1, then YEAR(E1) will return the year portion of the date. Month(E1)+1 will return the month portion of the date and advance it to the next month. The final argument, the 1 will select the first day of the next month. Then, finally, I subtract one from that date to convert 1/1/2002 to 12/31/2001.


Thanks to Steve H. for this out-of-the-box answer. It is unusual to consider the "0th" of a month, but it works!


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.