Here is a quick formula that will allow you to return the end of the month from any date in Excel.
Contents:
Example - Get End of Month from Date Using A Formula
Formula
= EOMONTH(date, 0)
Simply replace the "date" with any input date, either hard coded value or a cell reference, to return the last day of the month in which that date is found.
Explanation
The EOMONTH function's primary use is to return the last day of any month for a given date.
In this example, we simply need to use use any date and a 0 in the months argument spot.
The 0 is telling the function to return the end of the month that the date occurs in. A 1 would give the end of the next month, while a -1 would give the end of the previous month.
Example
How to Return the End of the Month from Any Date Using a Formula
Assuming the dates are listed in column B, we can return the end of the month from any date by dropping in this formula alongside them.
= EOMONTH(B3, 0)