Easy way to get the last day of the month in Excel

There are a lot of times I need to get the last day of a particular month.  One example I’ve used lately is trying to forecast sales for the remainder of a month using the average to date.  If it’s February 12, for instance, and the average daily sales so far are 12k.  I want to extrapolate that out for the rest of the month.  To do so, I need for Excel to know how many more days are remaining in the month- February.

Excel makes this very easy in one of two ways:

  1. EOMONTH (for end of month) Function: The EOMONTH function is the easiest and quickest method to obtain the last day of a given month.  The syntax for the function is EOMONTH (base, offset), where base is any date whose month you wish to obtain the last day.  Offset is used if you ever want to return the last day for a month other than the one in which the ‘base’ date lies.  For example, if you wanted to get the last day of (or number of days in) next month, you would use EOMONTH (Now(),1); for the last day of last month, EOMONTH (Now(),-1) would be used.  This function returns Excel serial number corresponding to that date, so you’ll need format the cell so that it displays a date.  Also, in versions of Excel prior to 2007, the EOMONTH formula is part of the Analysis Toolpak.  If you get an error, you probably do not have it installed.  See Microsoft’s instructions for installing the Analysis Toolpak.
  2. Another trick to get the last day of the month is to use the DATE Function, whose syntax is DATE(year,month,day).  The trick is to use zero for the day parameter.  For example, if you want the last day in February, 2011, you could use DATE(2011,3,0).

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: