This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Calculate the Last Day of the Month
Problem: I need to calculate the last of the month. What the heck was that poem?
Figure 540 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!
Figure 541 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:
Figure 542 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.
Figure 543 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).
Figure 544 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.
Figure 545 Get the end of month from N months from now.