Last Day of the Month + Useful Date Formula

How do I display the last date of a month in a cell?

By Neale Blackwood

There are two methods. The first uses Excel’s series feature. If you enter two dates, for example, 31/1/03 in A1 and 28/2/03 in B1, you select both cells and drag to the right using the Fill Handle (bottom right-hand corner of the selection). Excel will fill in dates with the last day of the following months. This method will also work for a specific date in the month.

The second method uses a formula. The following formula will display the date of the last day of the month of the date in cell B1, =DATE(YEAR(B1),MONTH(B1)+1,0)
(you may have to change the format to display the date)

The following date formulae may also be useful. They calculate results based on a date in B1:

Days elapsed this calendar year =B1-DATE(YEAR(B1),1,0)

Days until the end of the calendar year = DATE(YEAR(B1),12,31)-B1

Days in the month =DAY(DATE(YEAR(B1),MONTH(B1)+1,0))