About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


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.

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

By Bill Jelen on 13-Dec-2001 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.