# 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! Figure 533. 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 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:

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 535. 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)`.

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. Figure 537. Get the end of month from N months from now.