how can i get the last day of a month? Is there a function?

I think this is in the Analysis ToolPak: EOMONTH

Suppose your date is in cell B1, then use

=EOMONTH(B1,1)

1 refers to number of months after that given date in B1. If you want the same month, use 0. Be sure to format your cell as date, otherwise it will be a serial number.

Try this.

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