Chapter 12: Using Formulas with Dates and Times
12
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 12.10 illustrates how to get the last date in February for each date given in order to
see which years are leap years.
FIGURE 12.10
Calculating the last day of each date
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 12.10, keep in mind that you can use the formula to get the last day
of any month, not just February.