47. Excel Tips - Calculate the LAST Day of the month

Nick's picture

This is a follow-on from tip on how to calculate the 1st day of the month in Excel

Suppose we want to calculate the last day of the month... can we use the same function ?

YES, but we need to be a bit more cunning this time.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

  • will return the last day of the month.
  • it is basically saying take the 1st day of NEXT month, and subtract 1 day.

last-day-of-the-month-excel 

Download a spreadsheet to practice calculating the last day of the current month in Excel

It Even works for leap years.

 

Training video on calculating the last day of the month in Excel:

Calculate the last day of the month

Here's a shorter formula:

=EDATE(TODAY(),1)-DAY(TODAY())

Best Formula

=Eomonth(date,0)