Get last day of month

Get last day of month

Generic formula 

=EOMONTH(date,0)

Related formulas 

Get last working day in month

Explanation

The easiest way to calculate the last day of a month is to use the EOMONTH function, which is made for this purpose.

In the example shown, the formula in cell B5 is:

=EOMONTH(B5,0)

How this formula works

The 2nd argument (months) of the EOMONTH function allows you to get the last day of the month in future or past months. When you use zero for months, EOMONTH will return the last day of the month in the same month.

To get the last day of the prior month, use:

=EOMONTH(date,-1)

To get the last day of the next month, use:

=EOMONTH(date,1)

Clever alternative

If you're the type that likes to build clever formulas that puzzle others, you can also use the DATE function to get the last day of the month:

=DATE(YEAR(date),MONTH(date)+1,0)

The trick with this formula is supplying zero for the day. When you supply zero as the day argument to DATE, the date function will roll back one day into the previous month, to the last day of that month. So, by adding 1 to the month, and using zero for day, DATE rolls back to the last day of the "original" month.

Get last day of month

Generic formula 

=EOMONTH(date,0)

Related formulas 

Get last working day in month

Explanation

The easiest way to calculate the last day of a month is to use the EOMONTH function, which is made for this purpose.

In the example shown, the formula in cell B5 is:

=EOMONTH(B5,0)

How this formula works

The 2nd argument (months) of the EOMONTH function allows you to get the last day of the month in future or past months. When you use zero for months, EOMONTH will return the last day of the month in the same month.

To get the last day of the prior month, use:

=EOMONTH(date,-1)

To get the last day of the next month, use:

=EOMONTH(date,1)

Clever alternative

If you're the type that likes to build clever formulas that puzzle others, you can also use the DATE function to get the last day of the month:

=DATE(YEAR(date),MONTH(date)+1,0)

The trick with this formula is supplying zero for the day. When you supply zero as the day argument to DATE, the date function will roll back one day into the previous month, to the last day of that month. So, by adding 1 to the month, and using zero for day, DATE rolls back to the last day of the "original" month.