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).