Excel Formula for Next Month: Dates, Month-End, and More

Formulas & functions · Updated November 18, 2026

To move a date in cell A1 forward by one month, enter =EDATE(A1,1). Excel returns the corresponding date in the next month, keeping the day number when possible and using the last day of that month when the original day does not exist there.

Why EDATE Is the Standard Choice

With a valid Excel date in A1, =EDATE(A1,1) is the simplest formula for the same calendar day in the following month. For example, if A1 contains March 15, 2026, the result is April 15, 2026. The second argument is the number of months to move: 1 means next month, 2 means two months later, and a negative number moves backward.

EDATE works with Excel dates, which are stored as serial numbers and displayed according to the cell’s number format. The formula calculates a date; it does not automatically guarantee that the result will appear in a familiar date style. If the cell shows a number such as 46127, the calculation may be correct and the cell may simply be formatted as General or Number. Apply a date format to see a calendar date.

For the basic formula, the cell layout is a starting date in A1 and the formula in another cell, such as B1. If A1 is April 10, 2026, the result is May 10, 2026. The same formula works in Excel for Microsoft 365 on Windows, Mac, and the web; the formula itself does not change between those platforms.

How EDATE Handles Month Ends

Months have different numbers of days, so there is not always an exact same-numbered day to return. EDATE handles this by choosing the final valid day in the target month. With January 31, 2025 in A1, the formula =EDATE(A1,1) returns February 28, 2025. In a leap year, January 31, 2024 moves to February 29, 2024. Likewise, moving from March 31 to the next month returns April 30.

This behavior is useful for monthly due dates, recurring reviews, and billing dates when the intention is to use the same day number where possible, but it can surprise someone expecting a fixed 30- or 31-day interval. A month is a calendar unit, not a set number of days. Adding one month to January 31 is therefore not the same operation as adding 30 days.

It also matters when you fill a schedule from one result to the next. Suppose A2 contains January 31, 2025 and A3 contains =EDATE(A2,1). A3 becomes February 28. If you then put =EDATE(A3,1) in A4, the result is March 28, because the new starting date is now the 28th. If you need every period to be based on the original anchor date, calculate each row from that anchor instead of from the prior result. For example, with the original date in A2 and the month number 2 in B4, a formula in C4 can be =EDATE($A$2,B4), returning March 31, 2025. The dollar signs keep the anchor fixed when the formula is copied.

Choose What Next Month Means

The phrase next month can mean the same day next month, the first day of the next month, or its final day. Choose the formula that matches the task rather than treating these dates as interchangeable:

  • For the corresponding date next month, use EDATE with a one-month offset. With March 15, 2026 in A1, =EDATE(A1,1) returns April 15, 2026.
  • For the first day of next month, use EOMONTH to find the current month’s end and add one day. With any valid March 2026 date in A1, =EOMONTH(A1,0)+1 returns April 1, 2026.
  • For the last day of next month, use EOMONTH with an offset of one. With March 15, 2026 in A1, =EOMONTH(A1,1) returns April 30, 2026.

EOMONTH returns the final calendar day of a month a specified number of months away. The zero offset means the month containing the starting date; one means the following month. Adding one to the current month’s end is a convenient way to find the next month’s first day. These formulas return real date values, so they can be sorted, compared, or used in further calculations.

Another way to construct the first day of the next month is to use DATE. With a valid date in A1, =DATE(YEAR(A1),MONTH(A1)+1,1) returns the first day of the following month. DATE rolls the month number forward across year boundaries, so a December date produces January 1 of the next year. This option makes the desired day, 1, explicit; EOMONTH plus one is often easier to read if you already use EOMONTH for month-end calculations.

Use Today for a Moving Next-Month Date

If the starting point should always be the current date, replace the cell reference with TODAY. With no date input cell required, =EDATE(TODAY(),1) returns the same calendar day in the month after today. For example, if the formula recalculates on March 15, 2026, it returns April 15, 2026. TODAY updates as Excel recalculates, so the displayed answer changes over time rather than preserving the date on which you first entered the formula.

Use a month boundary instead if the task is to identify the next calendar month’s start or end. With no input cell required, =EOMONTH(TODAY(),0)+1 returns the first day of the next month. With no input cell required, =EOMONTH(TODAY(),1) returns the last day of the next month. These are useful for reports that should roll forward automatically, such as a worksheet that always displays the coming month’s reporting window.

Because TODAY is dynamic, it is not suitable when you need a permanent record of the date calculated on a particular day. In that case, enter a fixed date in a cell and refer to that cell, or copy the calculated result and paste it as a value. A formula based on TODAY can also appear not to update immediately if calculation is set to Manual; check Excel’s Formulas > Calculation Options setting if dates seem stale.

Build a Monthly Schedule

EDATE is useful for creating installment dates, appointment cycles, subscription review dates, and other calendar-based lists. Put the first date in A2. In A3, enter =EDATE(A2,1), then copy the formula down using the fill handle. Each row advances one month from the previous row. Excel adjusts the relative reference as the formula is copied.

For schedules tied to a fixed original day, it is safer to calculate each row from the initial date. Put the original date in A2 and enter the month offset 1, 2, 3, and so on in B2:B4. In C2, enter =EDATE($A$2,B2) and copy it down. If A2 is January 31, 2025, the rows with offsets 1, 2, and 3 return February 28, March 31, and April 30, 2025. Each result refers to the original January 31 date, so the schedule returns to the 31st when a later month has that day.

For a schedule of first-of-month dates, set the original start date in A2 and put 1 in B2, 2 in B3, and 3 in B4. In C2, enter =EOMONTH($A$2,B2-1)+1 and copy down. If A2 is January 31, 2025, the results are February 1, March 1, and April 1, 2025. Offsets are adjusted by one because an offset of zero from the anchor month identifies that month’s first day.

For a straightforward list, copying a formula down is usually easiest to audit. In Microsoft 365 or Excel 2021 and later, SEQUENCE can generate a set of month offsets for a dynamic array. With the starting date in A2 and the formula entered in B2, =EDATE(A2,SEQUENCE(12,1,0,1)) spills 12 dates beginning with the date in A2 and then advances month by month. If A2 is January 31, 2025, the first two results are January 31 and February 28, followed by March 31 only if each result is calculated from the original anchor; this particular array formula applies each offset to A2, so it returns March 31 as its third result. Dynamic array formulas need room to spill into the cells below.

Format and Troubleshoot the Result

If a formula returns a serial number, select the result cell and choose a date format from Home > Number. A short date is suitable for many lists; a custom format can show a month name or include the weekday. Formatting changes only how the stored date is displayed. It does not change which date EDATE calculated.

If Excel shows the formula itself instead of a result, check that the cell is not formatted as Text, remove any leading apostrophe, and re-enter the formula. Also check that Show Formulas has not been enabled. On Windows, Ctrl+` toggles formula display; on Mac, the equivalent shortcut is Control+`. If the cell contains a text date rather than a real date value, EDATE may return an error or interpret the input differently depending on its format and regional settings. Enter the date as a recognized Excel date, or convert the source data to real dates before relying on the calculation.

  • A #VALUE! error commonly indicates that the starting value is not a usable date or that an argument contains text Excel cannot interpret as a number.
  • A result on the wrong day may come from month-end adjustment, or from calculating each row from the previous adjusted result rather than from the original date.
  • A result that does not change when expected may be based on a fixed cell date rather than TODAY, or the workbook may be using manual calculation.
  • A formula that produces an unexpected year may be crossing December into January; check both the month and year rather than looking only at the month name.

Alternatives and Compatibility

DATE, EDATE, and EOMONTH solve related but different problems. DATE is helpful when you want to specify the year, month, and day directly, such as building the first day of next month. EDATE is the clearest choice for shifting a date by a number of calendar months while retaining its day where possible. EOMONTH is designed for month boundaries. Selecting the right function makes the formula easier for someone else to understand and reduces ambiguity around dates such as the 31st.

Excel for Microsoft 365 includes EDATE and EOMONTH as standard worksheet functions. The formulas here use commas as argument separators, as in the standard English-language Microsoft 365 setup. Some regional settings use semicolons instead; if Excel rejects a correctly structured formula, your list-separator setting may require semicolons. Do not confuse a display format with the underlying calculation: a cell may show only a month name while still holding a full date that includes a day.

If a time of day is stored alongside the date, it is represented as a fraction of a day. To move the date one month and retain that time, with a date-time value in A1, use =EDATE(INT(A1),1)+MOD(A1,1). INT supplies the date portion to EDATE and MOD retrieves the fractional time portion. For example, a source value representing January 31, 2025 at 9:00 AM will produce February 28, 2025 at 9:00 AM. Format the result cell to display both date and time if both matter.

Frequently Asked Questions

What formula gives the first day of next month in Excel?
With a valid date in A1, enter =EOMONTH(A1,0)+1. It returns the day immediately after the end of the month containing A1, which is the first day of the following month.

How do I get the last day of next month?
With a valid date in A1, enter =EOMONTH(A1,1). The result is the final calendar day of the next month, whether that month has 28, 29, 30, or 31 days.

Does EDATE keep the 31st when the next month is shorter?
With a date in A1, =EDATE(A1,1) returns the last valid day of the next month when the same day number is unavailable. January 31, 2025 therefore becomes February 28, 2025, not a date in March.

How can I calculate the same date two months later?
With the starting date in A1, use =EDATE(A1,2). The result is two calendar months after A1, subject to the same month-end adjustment. For a fixed anchor in a schedule, refer to that original date rather than repeatedly advancing the prior result.

Why does my next-month formula show a number?
With the formula in a result cell, select that cell and apply a date format from Home > Number. Excel stores dates as serial values, so a number display can be a formatting issue rather than a calculation error.

Related

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.