Excel EDATE Function: Add or Subtract Months from a Date

Use Excel’s EDATE function to move a date forward or backward by a specified number of whole months: enter =EDATE(start_date, months), replacing the arguments with a date and a month count. For example, if A2 contains January 31, 2024, =EDATE(A2,1) returns February 29, 2024. EDATE is useful for calculating renewal dates, payment schedules, review dates, and other dates based on monthly intervals.

This guide is for Microsoft Excel for Microsoft 365 on Windows. EDATE is available in current Excel for Mac and Excel for the web as well; the formula works the same way in those versions. Before you start, make sure your starting value is a valid Excel date, and format the result cell as a date so the returned date serial displays in a readable form.

What You’ll Need

A workbook with a valid starting date, either entered in a cell or supplied by another formula.
A whole-number month count, such as 6 to move six months forward or -2 to move two months back.
Basic access to Excel’s formula bar or a blank worksheet cell.

Step-by-Step

Enter or check your starting date. Put a date in a cell, such as A2, and confirm Excel recognizes it as a date rather than plain text. A date entered in a familiar format, such as 1/31/2024, is usually recognized according to your computer’s regional settings. If you are unsure, select the cell and choose Home > Number Format; selecting Short Date should display it as a date. You can also use a date created by a formula. The key is that EDATE needs a valid date value, not a label that only looks like one.
Choose the number of months to move. Decide whether the target date is earlier or later. Use a positive number to move forward and a negative number to move backward. For example, 3 means three months after the starting date, while -3 means three months before it. EDATE counts calendar months, not a fixed number of days, so adding one month does not always mean adding 30 days.
Select the cell where you want the answer. Click a blank cell, such as B2, and type the function with the starting-date cell and month count as arguments. The syntax is =EDATE(start_date, months). In Excel for Microsoft 365 on Windows, use commas between arguments as shown. Some regional settings use semicolons when entering formulas; if Excel rejects the comma, use the list separator configured for your installation.
Calculate a date one month later. With January 31, 2024 in A2, enter =EDATE(A2,1) in B2. The result is February 29, 2024, because 2024 is a leap year and February has 29 days. EDATE keeps the same day number when the destination month contains that day; when it does not, it uses the last valid day of that month. The returned value is a date serial, so the cell’s number format determines whether it appears as a date or as a number.
Format the result as a date if needed. Select the result cell and go to Home > Number Format, then choose Short Date or Long Date. You can also press Ctrl+1 on Windows to open Format Cells and select a date format. On Mac, use Command+1 to open Format Cells. If the cell shows a number such as 45351, the calculation may still be correct; change the number format rather than changing the formula.
Calculate a date in the past. Use a negative month count. For example, with June 15, 2025 in A2, enter =EDATE(A2,-3) in B2. The result is March 15, 2025. This is useful for finding a date three months before a review, follow-up, or planned event. Check that the negative sign is inside the formula’s month argument, and make sure the resulting date remains within Excel’s supported date range.
Use a month count stored in another cell. Keeping the month interval in a separate cell makes a calculation easier to update. With March 10, 2026 in A2 and the number 6 in C2, enter =EDATE(A2,C2) in B2. The result is September 10, 2026. Change C2 to another whole number to recalculate the target date without editing the formula. This setup is handy when a renewal term or follow-up interval may change.
Build a list of monthly dates from one anchor date. With January 31, 2024 in A2, enter =EDATE($A$2,ROWS($B$2:B2)-1) in B2, then fill the formula down the column using the fill handle. B2 returns January 31, 2024; the next row returns February 29, 2024; the following row returns March 31, 2024. The absolute reference $A$2 keeps the starting date fixed, while the ROWS expression increases the month offset as the formula is filled down. This generates each date from the original anchor, avoiding drift from repeatedly calculating from the previous result.
Review the result against your intended rule. Confirm both the month and day are appropriate for your task. EDATE is designed to preserve the day of the month where possible; it does not guarantee a specific number of days between dates or always return a month-end date. If the requirement is the last day of each month, use EOMONTH instead. If the requirement is a specific day, such as the 15th of every month, check that the starting date is the 15th before filling a series.

Common Mistakes

Expecting the same day number in every month. A date such as January 31 cannot become February 31, because that date does not exist. EDATE returns the last valid day of February, then uses that result as appropriate for its requested offset. For example, January 31 plus one month is February 28 in a non-leap year or February 29 in a leap year. It does not roll the extra days into March.

Confusing EDATE with EOMONTH. EDATE aims to retain the day number. EOMONTH returns the last calendar day of the target month. If A2 contains May 12, 2025, EDATE with one month gives June 12, 2025; EOMONTH with one month gives June 30, 2025. Choose based on the rule you need, not just because both functions accept a month offset.

Entering a date that Excel treats as text. A value may look like a date but be stored as text, especially if it was imported or entered in a format that conflicts with regional settings. EDATE may then return a value error. Check the cell’s alignment and number format, but do not rely on appearance alone. Re-enter the date using a format Excel recognizes or convert the source data to genuine Excel dates before calculating.

Using a decimal month count without intending truncation. EDATE truncates a non-integer month argument to an integer; it does not calculate a fraction of a month. For instance, a month count of 1.8 is treated as 1, and -1.8 is treated as -1. Use a whole number for predictable results. If your interval is expressed in days, use a day-based calculation rather than converting days to an approximate decimal number of months.

Reading a date serial as a wrong answer. Excel stores dates as numbers. If the result appears as a five-digit number, the formula may have calculated correctly but the cell is formatted as General or Number. Apply a date format to the result. Conversely, a date-like display does not prove the source cell is a valid date; inspect the input if an error occurs.

Tips

Keep the starting date in one clearly labeled cell and refer to it in formulas. This makes it easy to audit the calculation and revise the date without rebuilding each formula. If you are preparing a schedule, use a separate column for the month offset and another for the EDATE result. A visible offset such as 0, 1, 2, and 3 makes the sequence easier to review than a long series of manually typed dates.

Use an absolute reference for a fixed anchor when copying formulas, as in $A$2. Without the dollar signs, a relative reference changes as the formula is filled down, and the schedule may use a different starting date in each row. For a schedule that should follow the previous result instead, you can reference the previous date cell, but be aware that month-end dates can produce a different pattern. Starting from January 31 and repeatedly moving one month can yield February’s last day and then the 28th or 29th in later months. Generate each offset from the original anchor when you want the 31st wherever that day exists.

For month-end schedules, select EOMONTH rather than trying to adjust EDATE’s output manually. For the first day of a target month, use a separate date construction rule rather than assuming EDATE will change the day to 1. Also remember that date display is controlled by cell formatting: a formula’s underlying result and its visible format are separate. Choose a display such as 14-Mar-2026 when ambiguity between month and day could cause confusion.

When importing dates from another system, inspect a few source cells before filling formulas through thousands of rows. Mixed real dates and text dates can make a schedule partly calculate and partly fail. If a workbook is shared with people using different regional settings, use unambiguous date displays and keep the underlying values as Excel dates. Excel for Mac and Excel for the web use the same EDATE formula; ribbon layout and keyboard shortcuts can differ, but the calculation does not.

Troubleshooting

EDATE returns #VALUE!: Check that the start_date is a valid date value and that the months argument is numeric. A date stored as text, a text label such as “next month,” or a cell containing nonnumeric text can cause this error. Replace or convert the invalid input, then recalculate. If the date came from an import, use the source system’s date format or Excel’s available conversion tools to create real date values.

EDATE returns #NUM!: The requested result may fall outside Excel’s supported date range. Check for an unusually large positive or negative month count, and confirm that the start date is valid. Reduce the offset or correct the input so the resulting date is within the supported range.

The result is a number: Select the result cell and apply a date format from Home > Number Format. The formula can be correct even when the displayed serial number looks unfamiliar. If formatting does not turn it into the expected date, revisit the formula and the start-date cell.

The result is one day or one month off: Check the source date and the sign and value of the month argument. Confirm whether the task calls for an anniversary-style date, a month-end date, or a fixed number of days. EDATE does not account for business days, holidays, or a custom billing policy; those rules need additional logic. If a result is unexpected only for dates near the end of a month, compare it with the month-end behavior described above.

Excel does not accept the formula: Verify that the function name is spelled EDATE and that the parentheses are balanced. The formula must begin with an equals sign. If your regional settings require semicolons, replace the argument comma when typing the formula in that installation. In current Microsoft 365 Excel, EDATE is built in; in some older Excel releases, it may require the Analysis ToolPak add-in. If an older workbook reports an unrecognized function, check the Excel version and add-in availability.

Frequently Asked Questions

Does EDATE add a fixed number of days?
No. EDATE moves by calendar months. The number of days between the starting date and result varies according to the months involved and whether a leap day is included.

What happens when the target month has fewer days?
Excel returns the last valid day of that month. January 31 moved forward one month becomes February 28 in a non-leap year or February 29 in a leap year.

Can I use a negative number with EDATE?
Yes. A negative month argument moves the date backward. For example, -6 moves six calendar months before the starting date.

Does EDATE include the starting date as month zero?
A month offset of 0 returns the starting date itself. An offset of 1 returns a date one calendar month later, subject to the target month’s available days.

How is EDATE different from EOMONTH?
EDATE preserves the starting day number when possible, while EOMONTH returns the final day of the target month. Use EDATE for dates such as monthly anniversaries and EOMONTH for month-end deadlines or reporting periods.

Can EDATE calculate a date six months from today?
Yes. With no cell references needed, enter =EDATE(TODAY(),6) in a blank cell. It returns the date six calendar months after the current date, and it updates when Excel recalculates the TODAY function. Format the result cell as a date if it displays as a number.

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.