Problem: I need to calculate the last of the month. What the heck was that poem?
- Don't try the formula on the right.
Strategy: Don't try coding that poem about the number of days. The DATE() function will handle this easily, with one clever trick. Don't try to go to the 31st or 30th or 28th of the month. Instead, go to the first of the next month and then subtract 1!
- Go to the first of the next month and subtract 1.
This is a clever approach, isn't it?
How does it manage to work in December? You are asking for the first of the 13th month of 2015. Excel has no problem figuring out that =DATE(2015,13,1) is January 1 of 2016. In fact, here is the 37th day of the 18th month of 2020:
- The DATE function is incredibly versatile.
The DATE function can even sort of handle negatives, with one twist. A zero in the month or day argument is treated as "the item before 1". Thus, using -1 as the month will actually go back two months.
- To go back one month, use 0 as the month.
This previous trick makes the original question even easier. To go to the end of this month, you would go to the 0th of the next month. =DATE(YEAR(A2),MONTH(A2)+1,0).
- Shorter formula for end of month.
If you are sure you won't be sharing the workbook with anyone using Excel 2003, you can safely use the EOMONTH function to show the end of this month, last month, two months from now, and so on.
- Get the end of month from N months from now.