There are different methods to calculate the last day of a month. The easy way is to use the EOMONTH Function, which returns the last day of the month that is the indicated number of months before or after the start date.
1. EOMONTH Function
Please use the formula below to calculate the last day of the month.
=EOMONTH(A2,0)
Where A2 is the cell with the date and 0 means the current month.
You change 0 to any other numbers, where a positive number means the months after and a negative number means the months before.
For example, =EOMONTH(A2,3) will return the last day of the month 3 months after the date in cell A2.
2. DATE Function
The last day of a month varies month from the month, but the first day is always the same, so you can get the first day of the next month, then minus one day back to have the last day of the month.
=DATE(YEAR(A2),MONTH(A2)+1,1)-1
