Use Excel’s EOMONTH function to return the last day of a month: enter =EOMONTH(start_date, months), replacing the arguments with a date and the number of months to move. For example, with a date in A2, =EOMONTH(A2,0) returns the last day of the month containing that date.
What EOMONTH does
EOMONTH calculates a month-end date relative to a starting date. The second argument tells Excel how many months forward or backward to move before finding the last calendar day. Use zero for the starting date’s month, a positive number for a later month, and a negative number for an earlier month.
The function returns a date value, not a text label. That matters because Excel can use the result in date calculations, sorting, comparisons, and other formulas. It also means the cell might initially display a number rather than a familiar date if its number format is set to General. Changing the cell format to a date makes the underlying result easier to read.
Month-end dates are useful in recurring reports, billing schedules, inventory snapshots, project timelines, and accounting periods. Because EOMONTH accounts for different month lengths and leap years, it is usually more dependable than trying to add a fixed number of days.
How to write the formula
The syntax is EOMONTH(start_date, months). The first argument can be a cell reference, a date produced by another function, or a date entered as a valid Excel value. The second argument is a number of months, such as 0, 1, or -1. In Microsoft 365 for Windows, type the formula into a cell and press Enter; Excel calculates the result immediately.
For a robust example date, assume A2 contains a date created with =DATE(2025,3,14), which returns March 14, 2025. Then, with that date in A2, =EOMONTH(A2,0) returns March 31, 2025. The DATE function builds a date from year, month, and day components, avoiding ambiguity between regional date-entry styles such as 3/4/2025.
The months argument can also refer to another cell. With a starting date in A2 and a month offset in B2, use =EOMONTH(A2,B2). If B2 contains 2, the result is the last day two months after A2’s month. If B2 contains -2, it is the last day two months before. The argument can be calculated by another formula, too, provided the result is a number.
Examples for previous, current, and future months
Assume A2 contains March 14, 2025. The following offsets illustrate how the function works:
- With the date in A2,
=EOMONTH(A2,0)returns March 31, 2025. - With the date in A2,
=EOMONTH(A2,1)returns April 30, 2025. - With the date in A2,
=EOMONTH(A2,-1)returns February 28, 2025.
February shows why a calculated month-end is more reliable than adding 30 days: its length varies, and the last day can be February 28 or February 29. For a leap-year example, if A2 contains February 10, 2024, then with that date in A2, =EOMONTH(A2,0) returns February 29, 2024.
EOMONTH always returns the end of the target month; it does not preserve the day number from the starting date. A start date on the 14th and a start date on the 30th both lead to the same month-end when given the same month offset. This makes the function suitable for period boundaries rather than for calculating a date that keeps the same day of each month.
Use EOMONTH in monthly schedules
A common schedule starts with a month-end date and calculates each following month-end. If A2 contains March 31, 2025, then with the previous month-end in A2, =EOMONTH(A2,1) returns April 30, 2025. Fill the formula down the column to continue the sequence. Excel adjusts the relative reference from A2 to A3, A4, and so on.
If you need a month-end based on a fixed original date rather than the previous row, keep the reference to the source date fixed. With the original date in A2 and the offset number in B2, enter =EOMONTH($A$2,B2) in C2, then fill down as B2 changes to B3, B4, and later offsets. The dollar signs keep the reference to A2 from moving. This approach can help prevent small reference or rounding errors from accumulating through a long sequence.
You may instead need the first day of the next month. With a date in A2, =EOMONTH(A2,0)+1 returns the first day after the month-end. For March 14, 2025, the result is April 1, 2025. Excel stores dates as sequential numbers, so adding 1 to a date advances it by one day.
For date-based summaries, EOMONTH can define a period boundary. A month-end date can serve as a reporting label, while the first day of the month can serve as a start boundary. When filtering transactions, consider whether your criteria include times as well as dates: a transaction timestamp on the final day may include a time fraction, so a comparison that requires an exact match to the date value could miss it. A less-than comparison against the first day of the next month is often a practical way to include all times within a month.
Format the result as a date
If the formula displays a number such as 45747 instead of a date, the calculation may still be correct. Excel stores dates as serial numbers; the displayed date is controlled by the cell’s number format. Select the result cell, then use Home > Number Format and choose a date format. You can also use the Format Cells dialog to choose a display style, such as a short date or a custom format.
Formatting changes how the value appears, not the value returned by EOMONTH. For example, a cell formatted to display only the month and year can still contain the full month-end date. That full value remains available to formulas. If you need a text label instead of a usable date, a text-formatting function can make one, but text labels are less convenient for date arithmetic and chronological comparisons.
In Microsoft 365 for Windows, the Home tab contains the number-format controls. Excel for Mac and Excel for the web also support date number formats, although the ribbon layout and available format controls can differ slightly. The EOMONTH calculation itself works the same way across these versions.
Common errors and how to fix them
- #VALUE!: The start_date may be text that Excel cannot interpret as a date, or the months argument may not be a usable number. Check the source cells and, where possible, create dates with DATE rather than relying on ambiguous text entries.
- A number instead of a date: The result cell is probably formatted as General or Number. Apply a date number format; there is usually no need to change the formula.
- An unexpected month-end: Check the months argument and whether it is positive, zero, or negative. Also verify that the starting date is the date you intended, especially if it was imported from another system.
- A formula appears as text: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, re-enter the formula, and press Enter.
If the months argument is a decimal, Excel truncates the fractional part rather than treating it as a fraction of a month. For example, an offset of 1.8 is treated as 1 month. If your offset comes from a calculation, confirm that truncation matches your purpose; otherwise, deliberately round it before using it.
Excel workbooks can use different date systems, and imported data may contain dates stored as text or serial values from another application. If a result seems off by years or days, inspect the original value and its format before changing the formula. A date that merely looks correct is not necessarily stored as a date value.
EOMONTH compared with other date formulas
For month-end calculations, EOMONTH is clearer than manually constructing the last day with day counts or nested date arithmetic. Month lengths are not uniform, and leap years add another exception. The function expresses the intended operation directly: move by a number of months, then return that month’s final day.
The DATE function is useful when you want to construct a specific date from year, month, and day values. It can also normalize out-of-range date components, but that is a different task from asking for a month’s final day. EOMONTH is the more direct choice when the desired result is specifically the end of a month.
Another approach is to use the first day of the following month and subtract one day. With a date in A2, =DATE(YEAR(A2),MONTH(A2)+1,1)-1 returns the last day of A2’s month. This works because DATE adjusts a month value that moves beyond December, and subtracting one day reaches the preceding month’s end. In routine work, EOMONTH is shorter and makes the month-offset intention easier to recognize. The alternative can be useful when explaining the underlying date arithmetic or working in an environment without EOMONTH.
Availability and practical considerations
EOMONTH is available in current Microsoft Excel, including Microsoft 365 for Windows, Excel for Mac, and Excel for the web. In older Excel releases, the function could require the Analysis ToolPak add-in; in current Microsoft 365, it is a standard worksheet function. There is no special Mac-only or web-only version of the formula syntax.
When sharing a workbook with someone using older software, compatibility is worth checking, particularly if the recipient sees a function name prefixed with _xlfn. That prefix can indicate that the recipient’s Excel version does not recognize a function. For current Microsoft 365 users, EOMONTH is a familiar, established function and generally does not require a special setup.
For a dependable workbook, store actual dates in the source cells, use clear headers for month offsets, and format the output consistently. If users enter dates manually, state the expected date convention or use data validation to reduce accidental text entries. These small choices make a schedule easier to audit and less likely to produce confusing results.
Frequently Asked Questions
What is the formula for the end of the current month in Excel?
With a valid date in A2, use =EOMONTH(A2,0). It returns the final calendar day of the month containing A2. To calculate the current month based on today’s date instead, use =EOMONTH(TODAY(),0); TODAY returns the current date and updates when Excel recalculates the workbook.
How do I get the last day of next month?
With a starting date in A2, use =EOMONTH(A2,1). The result is the final day of the month after the one containing A2. The formula uses a month offset, so it does not assume that every month has the same number of days.
How do I get the first day of a month from EOMONTH?
With a date in A2, the first day of the following month is =EOMONTH(A2,0)+1. To get the first day of the month containing A2, use =EOMONTH(A2,-1)+1. For a date in March 2025, the latter returns March 1, 2025.
Why does EOMONTH show a serial number?
Excel stores dates as serial numbers, and the result cell may be formatted as General. Select the cell and apply a date number format from the Home tab. The formula can be correct even when the current display is not a readable date.
Can EOMONTH return the last business day of a month?
No. EOMONTH returns the last calendar day, even if it falls on a weekend or holiday. A business-day result needs additional logic, such as a workday function and a holiday list, because the appropriate adjustment depends on the weekend pattern and holidays you want to observe.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concat Excel Different Sheets: Formulas and ExamplesTo concatenate values from different Excel sheets, use CONCAT with a reference to each sheet, such as =CONCAT(Jan!A2,Feb!A2).…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- XLOOKUP Examples for Beginners: Formulas, Tips, and FixesXLOOKUP finds a value in one range and returns the corresponding value from another; for example, with product…
- IFERROR with AVERAGE in Excel: Formulas and ExamplesTo use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average…
- Excel IFS Function: What Happens If No Test Is TRUE?If every test in Excel’s IFS function is FALSE, the function returns #N/A; it does not have a…
- Excel DATEDIF Formula Examples for Age and Date GapsExcel’s DATEDIF function calculates the number of complete years, months, or days between two dates: use =DATEDIF(A2,B2,"Y") for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.