MacLochlainns Weblog

I had an interesting reaction when I told my students they had to create a dynamic Excel model that required them to enter the months of the year without AutoFill feature. They were stumped. They couldn’t find a function to perform it for them.

It’s really quite easy, the EDATE function lets you find it directly. Enter any day in the current month as the first parameter and one as the number of months to add as the second parameter to the function. Voilà, you have a function to add a month to any date. The only time it returns the first of the next month is when the source date was the first day of the current month.

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of February in the same year or February 14, 2011:

=EDATE(B1,1)

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of December in the prior year or December 14, 2010:

=EDATE(B1,-1)

You can also use the EOMONTH function to find the first or last day of the month. It lets you find it the first day of the next month regardless of the start date. All you need is a simple trick.

Enter any day in the current month as the first parameter and zero as the number of months to add as the second parameter to the function, and then add one.

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the last day of January in the same year (rounding up).

=EOMONTH(B1,0)

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the first day of January in the same year (rounding down).

=EOMONTH(B1,-1)+1

A subsequent question asked how to calculate the 15th day of the current month, and here’s the formula:

=EOMONTH("10-Jul-2012",-1)+15

Hope this helps them and others looking for the trick.