Excel Last Day of Month: Formula and Examples

To return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains a valid Excel date. The result is the month-end date, including February 29 in leap years; format the result cell as a date if Excel displays a number instead.

Use EOMONTH to find a month’s final day

EOMONTH is the standard Excel function for calculating a month’s final calendar day. It takes a starting date and a month offset. A zero offset means the same month as the starting date, so the formula in the opening example returns the month-end date corresponding to A2.

For example, if A2 contains February 15, 2024, EOMONTH returns February 29, 2024. If A2 contains February 15, 2025, it returns February 28, 2025. You do not need a separate formula for months with 30 days, months with 31 days, or leap-year February. Excel accounts for the calendar automatically.

To enter it in Microsoft Excel for Microsoft 365 on Windows:

Enter a real date in a cell, such as A2.
Select the cell where you want the month-end result.
Type the formula, using the cell that contains your date, and press Enter.
If the result appears as a serial number, apply a date format to the result cell.

When copying the formula down a column, Excel adjusts the relative reference from A2 to A3, A4, and so on. This is useful for a list of transaction dates, invoice dates, or reporting periods.

How the month offset works

The second EOMONTH argument tells Excel how many months to move from the starting date before finding the end of that month. Use zero for the current date’s month, a positive number for a future month, and a negative number for a past month. The offset is a count of whole months, not a number of days.

With the date February 15, 2024 in A2, =EOMONTH(A2,0) returns February 29, 2024. With the same date in A2, =EOMONTH(A2,1) returns March 31, 2024, and =EOMONTH(A2,-1) returns January 31, 2024. The starting day does not have to exist in the target month: Excel still calculates the final day of the month reached by the offset.

This makes EOMONTH useful for recurring monthly schedules. For example, if A2 holds a date in the first billing month, using an offset of 0, 1, 2, and so forth can produce consecutive month-end dates. If you fill formulas down, you can either increase the offset as you go or use a row-based calculation. The important point is that the function calculates from the original date and the specified offset; it does not add a fixed 30 or 31 days.

Examples for common month-end tasks

These examples assume the dates described are stored as actual Excel dates, not text that merely looks like a date. The output can be displayed in any suitable date format.

With March 8, 2026 in A2, =EOMONTH(A2,0) returns March 31, 2026.
With April 30, 2026 in A2, =EOMONTH(A2,0) returns April 30, 2026. A date already at month-end stays there.
With December 12, 2026 in A2, =EOMONTH(A2,1) returns January 31, 2027, crossing into the next year automatically.
With January 10, 2026 in A2, =EOMONTH(A2,-1) returns December 31, 2025.

These results can support monthly reporting, due-date calculations, and period labels. A month-end date is a real date value, so it can be sorted chronologically, used in comparisons, or referenced by other formulas. It is not just a text label such as the words March end.

Alternative formula using DATE

If you prefer not to use EOMONTH, Excel can calculate the same-month end by constructing the first day of the following month and asking for day zero. With a valid date in A2, use =DATE(YEAR(A2),MONTH(A2)+1,0). For example, if A2 is February 15, 2024, this returns February 29, 2024.

DATE normalizes its year, month, and day arguments. In this formula, adding one to the input month points to the next month, while a day value of zero resolves to the final day of the preceding month. The method works across year boundaries and leap years. It is a useful alternative when a workbook needs to express the calculation using the widely available YEAR, MONTH, and DATE functions.

For most worksheets, EOMONTH is clearer because its name directly describes the task and it accepts a month offset. The DATE method is not inherently more accurate. Choose one approach and use it consistently in a workbook so that another person can follow the logic easily.

Return the first or last day of a different month

EOMONTH can calculate a nearby month-end without changing the original date. For a date in A2, =EOMONTH(A2,1) gives the end of the following month, while =EOMONTH(A2,-1) gives the end of the previous month. This is often simpler and safer than trying to add a set number of days to the original date.

To get the first day of the month containing A2, use =EOMONTH(A2,-1)+1. For example, if A2 contains May 18, 2026, the result is May 1, 2026. To get the first day of the next month, use =EOMONTH(A2,0)+1; the same May date produces June 1, 2026.

These boundaries are handy for date-based summaries. When filtering records that may contain times as well as dates, a robust period condition is often to include dates on or after the first day of the month and exclude dates on or after the first day of the next month. That approach avoids accidentally leaving out records later in the final day because they include a time such as 4:30 p.m.

Format the result as a readable date

Excel stores dates as serial numbers so that it can calculate with them. A result that looks like 46113 is usually not a failed EOMONTH calculation; it is likely a valid date shown with a General or numeric format. Select the result cell and choose a date format from Home > Number Format, or open the Format Cells dialog.

On Windows, Ctrl+1 opens Format Cells. On Mac, Command+1 opens the same dialog. Choose a date category and a format that suits the worksheet, such as a short numeric date or a full month name. The underlying value remains a date; formatting changes how it appears, not what date the formula returns.

For a consistent display such as 31-Mar-2026, select the result cells, open Format Cells, choose Custom, and enter an appropriate format code such as dd-mmm-yyyy. A custom format is useful for reports where readers need to distinguish dates from ambiguous values such as 03/04/2026, which can mean different dates under different regional conventions.

Common problems and how to fix them

The formula returns a number. The result is probably displayed using General or Number format. Apply a date format; do not change the formula just to make the value look like a date.
The formula returns #VALUE!. Check that the starting-date cell contains a valid Excel date. A date imported as text may look correct but fail in a date calculation. Re-enter it as a date or convert the source data before relying on it.
The formula name is not recognized. Check the spelling of EOMONTH and the formula separators expected by your Excel regional settings. The examples here use commas, as requested for standard Excel syntax; some installations configured for different regional conventions may display or require semicolons.
The displayed date looks one day off. Check the source cell and the result’s number format, and confirm that the input is not text or an unexpected date value. If imported data includes times, EOMONTH returns a month-end date rather than preserving the input time.
A date appears as text in one system and a date in another. Date interpretation can depend on regional settings. Avoid ambiguous typed entries when sharing workbooks; use an unambiguous date entry or build a date from numeric year, month, and day values.

For very early dates, also consider the workbook’s date system and compatibility requirements. Excel workbooks can use the 1900 or 1904 date system, and the same serial number can represent a different calendar date under those systems. This is mainly relevant when copying date serials between workbooks or working with older files, rather than for ordinary formulas within one workbook.

Which method should you choose?

Use EOMONTH for the clearest general solution, especially when you may need to move backward or forward by months. Use the DATE alternative when it fits an existing formula pattern or when you want the year, month, and day logic to be explicit. Neither approach requires manually checking the number of days in a month.

For reporting, consider whether you need the date value or only a label. A true date value is best for sorting, comparison, and calculations. A displayed label such as Mar 2026 may be enough for presentation, but converting a date to text too early can make later date calculations harder. Keep the underlying result as a date and change its number format when the goal is simply a different appearance.

Frequently Asked Questions

What is the Excel formula for the last day of the current month?
With a date in A2, use =EOMONTH(A2,0). If you mean the actual current date without entering it in a cell, use =EOMONTH(TODAY(),0); TODAY returns the current date according to Excel’s recalculation.

Does EOMONTH handle leap years?
Yes. With February 15, 2024 in A2, =EOMONTH(A2,0) returns February 29, 2024. For February 15, 2025, it returns February 28, 2025. Excel determines the valid month length for the year.

How do I get the last day of the previous month?
With the relevant date in A2, use =EOMONTH(A2,-1). For example, if A2 contains March 8, 2026, the result is February 28, 2026.

Why does my month-end formula show a serial number?
Excel stores dates as numbers. Select the formula cell and apply a date format through Home > Number Format, or use Ctrl+1 on Windows or Command+1 on Mac to open Format Cells.

Can I use EOMONTH to find the first day of a month?
Yes. With a valid date in A2, =EOMONTH(A2,-1)+1 returns the first day of the same month. Adding one to the end of the current month instead returns the first day of the following month.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.