In this post you will learn how to return the last date of a given month in Excel.
Returning the last date of a given month –
A common need when working with dates is to calculate dynamically the last date in a
given month. Although the last day for most months is fixed, the last day for February var-
ies depending on whether the given year is a leap year.
Figure below illustrates how to get the last date in February for each date given in order to
see which years are leap years.
The DATE function lets you 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
date can be any positive or negative number.
For example, this formula would return the date serial number for December 1, 2013:
=DATE(2013, 12, 1)When you use 0 as the day argument, you are telling Excel that you want the day before
the 1st of the month. For instance, entering this formula into a blank cell will return
February 29, 2000:
=DATE(2000,3,0)In our example, instead of hard-coding the year and month, we use the YEAR function to
get the desired year and the MONTH function to get the desired month. We add 1 to the
month so that we go into the next month. This way, when we use 0 as the day, we get the
last day of the month in which we’re actually interested.
=DATE(YEAR(B3),MONTH(B3)+1,0)As you look at Figure above, keep in mind that you can use the formula to get the last day
of any month, not just February.
Using the EOMONTH function –
The EOMONTH function is an easy alternative to using the DATE function. With the
EOMONTH function, you can get the last date of any future or past month. All you need is
two arguments: a start date and the number of months in the future or past.
For example, this formula will return the last day of April 2015:
=EOMONTH("1/1/2015", 3)Specifying a negative number of months will return a date in the past. This formula will
return the last day of October 2014:
=EOMONTH("1/1/2015", -3)You can combine the EOMONTH function with the TODAY function to get the last day of the
current month.
=EOMONTH(TODAY(),0)