MrExcel.com - Your One Stop for Excel Tips & Solutions

Calculate the Last Day of the Month

June 23, 2022 - by

Calculate the Last Day of the Month

Problem: I need to calculate the last of the month. What the heck was that poem?

An ancient poem for remembering how many days are in a month is shown on the left. On the right, a very long Excel formula using CHOOSE, MONTH, IF, AND, MONTH, MOD, YEAR to simulate the poem. Luckily, there is an easier way.
Figure 532. 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!

With a date in D2, to find the end of the month, use =DATE(YEAR(D2),MONTH(D2)+1,1)-1. This gives you the first of next month and then subtracts one.
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:

=DATE(2025,18,37) resolves to the 37th day of the 18th month of 2025. That gets you out to July 7, 2026.
Figure 534. 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.

=DATE(2025,1,1) gets you January 1. =DATE(2025,0,1) gets you December 1 2024. =DATE(2025,-1,1) gets you back to November 1 2024.
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).

Three examples where =DATE(YEAR(D2),MONTH(D2)+1,0) gets you to the end of the month, even for dates in December.
Figure 536. Shorter formula for end of 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.

Starting in Excel 2007, the easier way to get to the end of this month is =EOMONTH(A4,0)
Figure 537. Get the end of month from N months from now.

This article is an excerpt from Power Excel With MrExcel

Title photo by Johannes W on Unsplash