How to write a formula to calculate the last day of the month for a given date

This article will describe a simple formula to calculate the last day of the month of any date.

The complication arises due to the fact that months may have 28, 29,30 or 31 days.

I will take you through the thought process of getting to the final formula.

Step 1: Set up the spreadsheet as can be seen below:

Step 2: In cell B2 we will start off with a simple date formula. =DATE(2009,6,4)

This will return a date “4 June 2009″. (The format may vary based on the date format of the cell). This formula consists of 3 parameters. Year,Month and Day and returns a result that is a date.

Step 3: One can also identify a Year, Month or Day from a date via the following formulas:

Year: =YEAR(A2)        This will return 2009

Month: =MONTH(A2)   This will return 6

Day: =DAY(A2)       This will return 4

Step 4: Lets now substitute the Year and Month (not the day) into our formula

= DATE(YEAR(A2),MONTH(A2),4)

This will still return the date “4 June 2009″. The challenge now is to substitute the “Day” portion of the formula with the last day of the month.

Since we do not easily know what the last day of the month is we can use the following logic:

Select the first day of the next month and then subtract 1 day. We would do this by adding 1 month to the month portion of the date formula like this:

• = DATE(YEAR(A2),MONTH(A2)+1,4)   This will return 4 July 2009 in our example.

We can then hardcode the first day of the month as it will always be 1, like this:

• = DATE(YEAR(A2),MONTH(A2)+1,1)   This will return 1 July 2009 in our example.

The final step would be to subtract 1 from the final date and you will be left with the
last day of the previous month:

• = DATE(YEAR(A2),MONTH(A2)+1,1)-1   This will return 30 June 2009 in our example.

You can view the final formula in the spreadsheet image below.

Keep Excelling!!