Use Excel’s EDATE function to move a date forward or backward by a specified number of months. If the starting date is in A2 and you want to add three months, enter =EDATE(A2,3); for example, January 15, 2025 becomes April 15, 2025.
EDATE syntax and what its arguments mean
EDATE has two arguments: a starting date and the number of months to move. The month count can be positive to move forward, negative to move backward, or zero to return the original date.
- start_date: A valid Excel date, usually held in a cell. It can also be created with the DATE function.
- months: The number of months to add or subtract. EDATE truncates a decimal month count to an integer; it does not calculate a fraction of a month.
With the starting date in A2 and a three-month interval, use =EDATE(A2,3). With the same date in A2 and a six-month move backward, use =EDATE(A2,-6). If A2 contains July 15, 2025, the second formula returns January 15, 2025.
The result is an Excel date serial value. Excel normally displays it as a date, but if the result appears as a number, the calculation may still be correct: the cell is likely formatted as General or Number rather than as a date.
Examples for adding and subtracting months
EDATE is useful when the interval comes from another cell, such as a contract term, reporting period, or reminder offset. With a starting date in A2 and the number of months in B2, use =EDATE(A2,B2). For a date of March 10, 2025 in A2 and 4 in B2, the result is July 10, 2025. Enter -2 in B2 instead, and the result is January 10, 2025.
You can also supply a date directly through DATE. With no input cells needed and January 31, 2025 as the intended start date, use =EDATE(DATE(2025,1,31),1). The result is February 28, 2025. DATE is useful when you want to make the year, month, and day explicit rather than rely on how a typed date is interpreted.
A one-year shift means 12 months. With the date in A2, use =EDATE(A2,12). This advances the date by a calendar year while keeping the same day of the month when possible. That is different from adding a fixed number of days, because calendar months have different lengths.
To create a list of monthly dates, put the first date in A2. With A2 holding the first date and the next result going in A3, enter =EDATE(A2,1) in A3, then fill the formula down. Each row advances one month from the previous row. Be aware that if the first date is at the end of a long month, later dates can shift to an earlier day; see the month-end section below.
How EDATE handles short months and leap years
EDATE tries to keep the original day number. When that day does not exist in the destination month, it returns the last valid day of that month. For example, moving one month from January 31, 2025 produces February 28, 2025. Moving one month from January 31, 2024 produces February 29, 2024, because 2024 is a leap year.
This rule also matters when chaining monthly dates. Suppose A2 contains January 31, 2025. With the starting date in A2 and a two-month offset, =EDATE(A2,2) returns March 31, 2025. But if you put February’s result in A3 and then calculate the next month from A3, EDATE moves from February 28 to March 28. The starting point has changed, so the original day of 31 is no longer available to preserve.
Choose the setup that reflects the real schedule:
- For a date tied to a particular day number, such as the 31st when possible, calculate each period from the original date and change the month offset for each row.
- For a schedule that should always use the last day of each month, use EOMONTH instead of EDATE.
- For ordinary monthly dates such as the 10th, filling down from the previous result works as expected because that day exists in every month.
Formatting and entering the formula
In Excel for Microsoft 365 on Windows, enter the starting date in a cell, select the cell where you want the result, type the formula, and press Enter. To display a numeric result as a date, select the result cell and choose Home > Number Format, then select a date format such as Short Date. Formatting changes how the serial value looks; it does not change the calculated date.
The same EDATE formula syntax works in Excel for Microsoft 365 on Mac and Excel for the web. The ribbon layout can vary slightly by platform, but look for the Number Format controls on the Home tab to change the display. If your regional settings use semicolons as formula separators, Excel may display or require semicolons where examples here use commas.
For typed dates, use a format Excel recognizes in your region, or build the date with DATE. For example, 4/5/2025 can mean April 5 or May 4 depending on regional settings. A cell that only looks like a date may also contain text rather than an actual date value, which can prevent date calculations from working reliably.
EDATE compared with EOMONTH, DATE, and adding days
EDATE and EOMONTH both move by whole months, but they answer different questions. EDATE aims to preserve the day of the month; EOMONTH returns the last day of the target month. If A2 contains January 15, 2025 and you want the end of the following month, with the starting date in A2 use =EOMONTH(A2,1). The result is February 28, 2025.
DATE is a better choice when you need to assemble a date from separate year, month, and day values, or when you need to set a particular day rather than shift an existing date. It also normalizes out-of-range month numbers: for instance, month 13 rolls into the next year. EDATE is more direct when the request is simply “this date, a certain number of months later.”
Adding a fixed number of days is not equivalent to adding months. With A2 containing January 15, 2025, the expression =A2+30 returns February 14, 2025, while moving one calendar month with =EDATE(A2,1) returns February 15, 2025. The 30-day approach can vary from month to month and is unsuitable when a schedule is defined in calendar months.
Common errors and troubleshooting
- The result is a number: Apply a date number format. Excel stores dates as serial values, so a number display does not necessarily mean the formula failed.
- The formula returns #VALUE!: Check that the starting value is a valid date and that the months argument is numeric. A date-looking text entry may not be recognized consistently.
- The answer is on an unexpected day: Check whether the target month has that day. EDATE uses the last day available when the original day does not exist.
- The date moves too far or not far enough: Check the sign and value of the months argument. A negative value subtracts months; a decimal is truncated rather than treated as a partial month.
- The result is an error near Excel’s date limits: A calculation that produces a date outside Excel’s supported date range cannot return a normal date.
When a formula gives an unexpected result, check the actual cell value and number format separately. Selecting a cell and looking at the formula bar can help distinguish a true date value from text that only resembles one. Also verify that a copied formula points to the intended input cell; relative references change as a formula is filled down.
When EDATE is useful
EDATE suits recurring calendar intervals: review dates, subscription renewal schedules, project milestones, follow-up reminders, and installment calendars. For example, if a policy begins on a date in A2 and renews every six months, place 6 in B2 and use EDATE with those cells. This creates a date based on the calendar rather than an assumed number of days.
For a schedule with several intervals, putting the month count in a separate cell makes changes easier. A user can change a period from 3 to 6 without editing the formula itself. Before relying on a generated series, decide how month-end dates should behave and verify a few results around February and 30- or 31-day months.
EDATE calculates dates only. It does not account for business days, holidays, contract-specific rules, or whether a date should shift to the preceding or following workday. If those rules matter, additional logic is needed. For money or contract calculations, treat spreadsheet results as arithmetic examples and check the actual terms with the relevant provider or a qualified professional.
Frequently Asked Questions
Does EDATE add days or months?
It adds or subtracts whole calendar months. Use date addition only when the interval is a specific number of days; a month does not always contain the same number of days.
How do I subtract months with EDATE?
Use a negative months value. For a date in A2 and a three-month move backward, enter =EDATE(A2,-3). If A2 is June 20, 2025, the result is March 20, 2025.
Why does EDATE return the last day of February?
If the starting day does not exist in the target month, EDATE uses that month’s final day. January 31 shifted one month lands on February 28 in 2025 and February 29 in 2024.
How can I always get the last day of a month?
Use EOMONTH, which returns a month-end date rather than trying to preserve the starting day. It is the clearer choice for month-end schedules.
Can EDATE use a number of months from another cell?
Yes. Put the start date in one cell and a positive or negative month count in another, then refer to both in the function. This makes the interval easy to adjust without rewriting the formula.
Related
- Convert XLS to Excel: How to Save as an XLSX Workbook
- Convert XML to Excel: Import, Clean, and Save Data
- Convert ZIP to Excel: What Works and What Does Not
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Unique Excel List Creation: Formulas, Tools, and TipsTo create a unique list in Microsoft Excel for Microsoft 365 on Windows, enter =UNIQUE(A2:A100) in an empty…
- Excel SUBSTITUTE Double Quote: Formulas and ExamplesTo remove straight double quotation marks from a cell in Excel, use =SUBSTITUTE(A2,CHAR(34),"") when the text is in…
- How to Calculate Row Totals in Excel with SUM FormulasTo calculate a row total in Excel, add a SUM formula in the cell where you want the…
- COUNTIF in Excel: How to Count Matching CellsIn Excel, use COUNTIF to count cells that meet one condition. With statuses in A2:A10 and four cells…
- Compare Values on Two Excel Sheets: Formulas and MethodsTo compare values in the same positions on two Excel sheets, enter a formula such as =IF(Sheet1!A2=Sheet2!A2,"Match","Different") in…
- Excel Filter No Duplicates? Show Unique ValuesExcel’s regular AutoFilter does not have a one-click option to show only unique values. To hide duplicate records…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.