Use the DATE Function to Find the Last Day of a Month

by Gregory on October 23, 2010

Last Day of February in the next several years

Zero has a special meaning in the Excel date system and can be used in the DATE Function to return the last day of the month. The simplest way to show this is to use a formula to find the last day of February in the coming years.

With cell A2 containing the current year 2010, the following formula is entered into cell B2:

=DATE(A2,3,0)

and returns the result 2/28/2010.

There are three arguments for the DATE Function: Year, Month, Day. Notice that the Month argument is 3, which is March, and the Day argument is zero (0).

DATE Function Dialog Box

The DATE Function will return the last day of the month prior to the Month argument, by using zero (0) for the Day argument.

Bonus Formula – The DAY Function

To see how many days are in February — shown above in column C  — wrap the previous formula inside the DAY Function.

=DAY(DATE(A2,3,0))

Related Posts Plugin for WordPress, Blogger...
Chandoo October 24, 2010 at 11:33 pm

Wow.. I did not know that…

Gregory October 26, 2010 at 8:29 am

Subtracting a day from any Date value results in the day before, and subtracting 1 from the 1st day of the month is the same as 1 -1 = 0.

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: