Microsoft Excel Help: Calculate Last Day Of Month
Strategy: Finding the last day of the month is trickier than finding the first day of the month. For the first day, you are always looking for a day of 1. For the last day, you might be looking for 31, 30, 28, or even 29 in February during leap years. Excellers have tried many different tricks for this problem. Many first attempts involve testing to see if the MONTH(A2) is equal to 1, 3, 4, 7, 8, 10, or 12 to assign a final date of 31. As you can imagine, this nested IF statement gets rather large.
One day this was being discussed on the MrExcel board and Aladin Akyurek chimed in with his elegant solution. Aladin pointed out that it is easy to figure out the first date of the next month. After you have figuredthis out, you can simply subtract one from the first of next month to get the date for the last of this month. Here is a table showing this logic.
To figure out the first of the next month, you will use the DATE function. This function requires three arguments, the Year, the Month, and the Day. You know that the Day will be 1. The Month should be the month of the date + 1. The year should be the year of the date. It is obvious this will work in the first row of our example, as shown in Fig. 541.
=YEAR(A2) is 2005.
=Month(A2) is 1. =Month(A2)+1 is 2.
=DATE(2005,2,1) will return February 1, 2005.
=DATE(2005,2,1)–1 will return January 31, 2005.
However, it is not so obvious that this formula would work if the Date in A2 were December 5, 2005. In this case, you would have:
=YEAR(A2) is 2005.
=Month(A2) is 12.
=Month(A2)+1 is 13.
Thus, you are asking for a date of =DATE(2005,13,1). It would seem like this would not work! What is the first day of the thirteenth month of 2005? Amazingly, Excel handles this with ease. As shown in Fig. 542, Excel returns a value of January 1, 2006.
Then, subtract 1 from the result. =DATE(Year(A2),Month(A2)+1,1)–1 will return 12/31/2005, which is the correct last day of the month, as shown in Fig. 543.
I’ve said it before, but the fact that Microsoft allows the DATE function to correctly return the forty-seventh day of the eighteenth month of 2005 is miraculous and incredibly useful. See the result in Fig. 544.
So, the formula used in cell E2 in Fig. 545 can be copied down to all rows of the dataset. Your new column E now calculates the last day of the month for each date.
Summary: To find the last day of a month, use the DATE() function to calculate the first of the next month and then subtract one day. This sure-fire method will find the last day, even if it falls on the 31st, 30th, 28th, or 29th.
Functions Discussed: =DATE(); =YEAR(); =MONTH()
Images
