Using EOMONTH

There is a slightly shorter method, but it requires the Analysis Toolpak. To install it, select the Office button → Excel Options → Add-Ins, ensure that the Manage box displays Excel Add-Ins, and press Go button (pre-2007, select Tools → Add-Ins and check Analysis Toolpak).

Then, you can use the EOMONTH function as shown here:

EODATE returns the last serial number of the last day of the month before or after a specified number of months, so in this case it will return 30 June 2007.

EODATE will only work if the Analysis Toolpak is installed on your PC, so if you email a spreadsheet to someone who does not have the Analysis Toolpak installed, EODATE will return #NAME.