Get last day of month

In this example, the goal is to get the last day of the month based on any valid date. This problem can be solved most easily with the EOMONTH function. However, it can also be solved with the DATE function as explained below.

The EOMONTH function

The EOMONTH function returns the last day of the month, a given number of months in the past or future. For example, with a start date of January 15, 2024, EOMONTH will return the following results with months set to -1, 0, and 1:

=EOMONTH("15-Jan-2024",-1) // returns 31-Dec-2023
=EOMONTH("15-Jan-2024",0)  // returns 31-Jan-2024
=EOMONTH("15-Jan-2024",1)  // returns 29-Feb-2024

Note that the start date remains the same, but the month varies. Negative months cause EOMONTH to move back in time, and positive months move forward, but the result is always the end of the month. The EOMONTH function automatically handles leap years - for example, February 2024 returns 29-Feb-2024 since 2024 is a leap year.

How the formula works

The formula =EOMONTH(B5,0) works by taking any date and returning the last day of that same month. Working with a start date of January 12, 2024:

=EOMONTH("12-Jan-2024",0)  // get last day of current month
="31-Jan-2024"  // final result

The result is always the last day of the month for any given date. As the formula is copied down, the process is repeated for each date in column B.

Last day of current month

To get the last day of the current month, you can use the same approach but replace the date reference with the TODAY function:

=EOMONTH(TODAY(),0)

This formula will automatically update each day to show the last day of whatever month it currently is. For example, if today is June 21, 2025, the formula will return June 30, 2025. If you open the same worksheet in July, the result will be July 31, 2025. The formula works exactly the same way as the main example:

=EOMONTH(TODAY(),0)
=EOMONTH("21-Jun-2025",0)  // get current date
="30-Jun-2025"  // last day of current month

This approach is useful in reports and dashboards that need to display current-month information. Since TODAY recalculates whenever the worksheet recalculates, this formula will always return the last day of the current month.

Variations

You can easily change how this formula works by changing the number of months used inside the EOMONTH function. The pattern EOMONTH(date, n) will give you the last day of the month that is n months away from your reference date:

=EOMONTH(B5,-1) // last day of previous month
=EOMONTH(B5,0)  // last day of current month
=EOMONTH(B5,1)  // last day of next month
=EOMONTH(B5,6)  // last day of month 6 months forward
=EOMONTH(B5,-6) // last day of month 6 months ago

To move forward or backward n months without changing the date, see the EDATE function.

Alternative formula

You can also write a formula using the DATE, YEAR, and MONTH functions to return the last day of the month:

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

This formula works in three steps:

1. Get the year from the date with YEAR(B5).
2. Get the month from the date with MONTH(B5) and add 1 to move to the next month.
3. Use 0 for the day, which causes DATE to "roll back" one day to the last day of the previous month.

For example, with a start date of January 12, 2024:

=DATE(YEAR("12-Jan-2024"),MONTH("12-Jan-2024")+1,0)
=DATE(2024,1+1,0)
=DATE(2024,2,0)
="31-Jan-2024"

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 to the last day of the previous month. So, by adding 1 to the month and using zero for day, DATE returns the last day of the "original" month.

Although EOMONTH is more convenient, this formula shows that there are many ways to solve the same problem in Excel. It also demonstrates that Excel treats day 0 in a month as the last day of the previous month, which can be useful in certain calculations.