Excel Last Day of Month Formula

The quick answer to finding the last day of the month is to use the EOMONTH formula, which is part of the Analysis ToolPak Add-in. For example, to determine the serial date number for the last day of the current month, use

=EOMONTH(NOW(),0)

To avoid using the EOMONTH formula, you can use the fact that the zeroth day of the next month is the last day of the current month:

=DATE(YEAR(NOW()),MONTH(NOW())+1,0)

There are many ways to do this, such as subtracting a day from the first day of the next month:

=DATEVALUE((MONTH(NOW())+1)&"/1/"&YEAR(NOW()))-1

Of course, NOW() could be replaced with a reference to a date cell, or in the case of the last formula ...

=DATEVALUE((month+1)&"/1/"&year)-1

where month and year are named references to cells.