How to round off to the nearest month in Excel?

Question

I want to round up today's date to the nearest month. I used Today() to get the current date

I used the formula =MONTH(EOMONTH(L2,DAY(L2)>15)+0)) but it keeps showing an error saying "There's a problem with this formula. Not trying to type a formula? When the first character is an equal or minus sign, excel thinks its a formula"

Answer 1

Fun, You're just missing a parenthesis in there. Give this a try:

=MONTH(EOMONTH(L2,(DAY(L2)>15)+0))

Hope this helps.

Answer 2

This formula should do the job.

=IFERROR(DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>15,1,0),1),"")

For today (May 7) it will return May 1. To make it return the end of the month, replace the final 1 in the formula with 0. That would return April 30 today. You can replace all references to cell A1 with TODAY().