Return last day of the month


The most efficient way to return the last day of any month.

Let’s assume A1 has a date in it, and you want the last day of that month:

=DATE(YEAR(A1);MONTH(A1)+1;0)

There is a slightly shorter method but it can only be used when/if the Analysis Toolpak is installed. That is, Tools>Add-ins and check Analysis Toolpak. Then you you can use the EOMONTH function as shown below

=EOMONTH(A1;0)