Calculate the Last Day of the Month
June 23, 2022 - by Bill Jelen
Problem: I need to calculate the last of the month. What the heck was that poem?
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!
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 2023. Excel has no problem figuring out that
=DATE(2023,13,1) is January 1 of 2023. In fact, here is the 37th day of the 18th month of 2020:
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.
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.
If you are sure you won’t ever have to save the workbook as an XLS file, you can safely use the
EOMONTH function to show the end of this month, last month, two months from now, and so on.
This article is an excerpt from Power Excel With MrExcel