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:

=DATE(YEAR(E1),1+MONTH(E1),1)-1

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.