Microsoft Excel How-to: End Of Month (EOMONTH) Function
A week ago or so E happens to mention a project that she has taken on at work. It was fairly complex, and for one part of it she needed to be able to dynamically update a row with the date for the end of the month, for the next 12 months. She wanted to be able to enter in a starting date, then have the adjacent 12 cells fill in automatically with the last day of the month for the next 12 months. I knew this could be done with a fairly complex formula, and certainly with some VBA code. But it seemed to me I had run across an end of month function previously in Excel, and with just a little bit of investigation I found it. Strangely enough, it's called EOMONTH, and here's how you can find it and use it.
The example shown uses Excel 2003 running on Windows XP. However, the eomonth function has been available since Excel 97, and should work the same regardless of the operating system (according to Microsoft).
The syntax for the function is:
The start_date is the base date it will use for the calculation, and the months is the number of months it will add to the base month. Fairly straightforward, but you do need to be aware of a couple of things. First, the EOMONTH function may not be installed and available on your system (which is easily remedied). Second, start_date must be a date, not text (which is a simple formatting issue). Let's look at how to set this up.
The first thing we need to do is to enter a date into a cell on a blank spreadsheet:
This should default to the date format, but let's just make sure. Right click on the cell, then select 'Format cells...' from the popup menu:
From the Format Cells dialog form click on the Number tab, then make sure that Date is selected in the Category: box. The selection in the Type: box does not really matter, and will default to options in your regional settings configuration (more about that at some other time). You can see mine has defaulted to the typical month/day/year format we use here in the states:
Click OK to close the dialog (if you had to change the format on the cell you will need to re-enter the date). Now, let's enter the formula [ =EOMONTH(B2,1) ] into the next cell in the same row. Type in [ =eomonth( ]:
and now move your mouse over the first cell where you entered the date, and click once:
then type in a comma [ , ], then a [ 1 ], then a closing parenthesis [ ) ]:
then hit the enter key. If you're like the majority of Excel users out there, you're going to see this:
The #NAME? error means that Excel can't figure out the formula you've entered into the cell. In this particular case it's because we need to install the Analysis Toolpack Add-In. It's quite simple, just select Tools -> Add-Ins... from the main menu:
then select the Analysis ToolPak from the Add-Ins dialog, and click on OK:
Now, Excel has it locked into its head that you've entered in an incorrect formula, and won't be able to get over it. So you will need to re-enter the exact same formula we just typed in, just type it in right over the old formula in the same cell, then hit enter. When you do you'll probably see something similar to this:
Oh, great, now what? Well, the EOMONTH function returns the serial number of the date. I'm not going to get into the explanation for that right now, suffice it to say that it is easier for Excel to make date calculations on this serial number. To get it into a format that means something to the average human being simply format the cell as a date. Easiest way is to use the format painter. Click once on cell B2, then click on the format painter icon from the main toolbar, then click on cell C2 to paste the format (there's another article explaining the format painter in excruciating detail here). After you've done that you should have something that looks like this:
Ah, that's more like it. Let's expand the formula to the right a couple of cells by placing your mouse over the expand button in the bottom right corner of cell C2 until the mouse cursor turns into a cross. Now left click once, and while holding down the left mouse button drag over 3 or 4 cells to the right:
Once you've done this you will see that Excel has filled in the cells with the last day of the month for the following months:
If you change the value of the first cell, all of the adjacent cells with be updated to reflect the correct new values.