Returning the Last Date of a Given Month in Excel

A common need when working with dates is to dynamically calculate the last date in a given month. Of course, although the last day for most months is fixed, the last day for February varies depending on whether the given year is a leap year. You’ll be able to spot the leap years in the results you produce from the formula.

The figure shows how to get the last date in February for each year given in order to see which years are leap years.

image0.jpg

As you look at the figure, keep in mind that you can use the formula to get the last day of any month, not just February.

The DATE function allows you to build a date on the fly using three arguments: the year, the month, and the day. The year can be any whole number from 1900 to 9999. The month and day can be any positive or negative number.

For example, this formula returns the date serial number for December 1, 2013:

=DATE(2013, 12, 1)

When you use 0 as the day argument, you tell Excel that you want the day before the first day of the month. For instance, entering the following formula into a blank cell returns February 29, 2000:

=DATE(2000,3,0)

In this example, instead of hard-coding the year and month, you use the YEAR function to get the desired year and the MONTH function to get the desired month. You add 1 to the month so that you go into the next month. This way, when you use 0 as the day, you get the last day of the month that you’re actually interested in.

=DATE(YEAR(B3),MONTH(B3)+1,0)