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.

## How to Find in Excel the Last Day of Month

If you want to find in Excel the last day of month 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***. Notice that the Month argument is 3, which is March, and the Day argument is zero (0).*

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

*argument.*

**Day**### 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))

Chandoo says

Wow.. I did not know that…

Gregory says

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.