Use Excel’s EDATE function to move a date forward or backward by a chosen number of months: enter =EDATE(start_date, months), replacing the arguments with a starting date or cell reference and a month count. For example, if A2 contains January 31, 2024, =EDATE(A2,1) returns February 29, 2024.
What EDATE does
EDATE calculates a date a specified number of calendar months before or after another date. It is useful for recurring schedules where the interval is measured in months, such as subscription renewals, project milestones, payment dates, or review reminders. The result is an Excel date value, not simply a date-looking piece of text.
The function has two arguments: start_date is the date from which to count, and months is the number of months to move. A positive month count moves forward; a negative count moves backward; zero returns the starting date. You can enter a date directly, refer to a cell containing a date, or build a date with Excel’s DATE function. Referencing a cell is often easier to maintain because you can change the starting date without editing the formula.
EDATE counts whole calendar months rather than fixed blocks of 30 days. Moving one month from March 10 produces April 10, even though the two dates are not exactly 30 days apart. That distinction matters whenever a schedule is based on calendar dates rather than a fixed number of days.
How to use EDATE in Excel
In Excel for Microsoft 365 on Windows, put a valid date in a cell, select the cell where you want the result, type the formula, and press Enter. For example, with a starting date in A2 and the number of months to move in B2, use this formula in C2:
With the starting date in A2 and the month offset in B2, enter this in C2. If A2 is January 15, 2024, and B2 is 3, the result is April 15, 2024:
=EDATE(A2,B2)
To enter the number of months inside the formula instead, use this setup: with January 15, 2024, in A2, enter the formula in B2. It returns February 15, 2024:
=EDATE(A2,1)
To move back three months from the same starting date, with January 15, 2024, in A2, enter this formula in B2. It returns October 15, 2023:
=EDATE(A2,-3)
EDATE works the same way in Excel for Mac and Excel for the web; the formula and comma-separated arguments do not change. If your regional Excel settings use semicolons as formula separators, Excel may display or require semicolons instead of commas. Use the separator accepted by your installation.
Practical examples for dates and schedules
EDATE can create a one-off future date, but it is also useful for building a sequence. Suppose A2 contains January 31, 2024, and you want a list of monthly dates beginning in B2. Enter the following in B2, then fill it down. B2 returns February 29, 2024; B3 returns March 31, 2024; and B4 returns April 30, 2024:
With the starting date in A2 and the first schedule result in B2, enter this in B2 and copy it down:
=EDATE($A$2,ROWS($B$2:B2))
The ROWS portion increases the month offset as the formula is filled down: it is 1 in row 2, 2 in row 3, and 3 in row 4. The dollar signs keep the original date and the first row of the sequence fixed while the formula is copied. This approach also makes the schedule easy to update: change A2, and the dates recalculate.
For a quarterly schedule, use an offset of three months for each step. With a starting date in A2 and the next quarterly date in B2, enter this in B2. If A2 is January 15, 2024, the result is April 15, 2024:
=EDATE(A2,3)
Common uses include:
- Setting a follow-up date one, three, or six months after a meeting.
- Calculating a contract or subscription renewal date from its start date.
- Creating monthly or quarterly review dates from a single anchor date.
- Finding a date a fixed number of months before a deadline by using a negative offset.
These examples calculate dates; they do not determine which date a contract, billing policy, or other agreement requires. Check the applicable terms when a schedule has business rules such as weekend adjustments or special billing conventions.
Month-end dates and a common misconception
When the target month does not contain the starting day, EDATE returns the last valid day of that target month. For example, January 31 moved forward one month becomes February 28 in a non-leap year, or February 29 in a leap year. It does not spill into March. This behavior is useful for preserving an end-of-month date in some situations, but it can surprise people who expect every result to land on the same numbered day.
A related detail matters when you build a sequence from the previous result. Starting with January 31, 2024, and repeatedly adding one month gives February 29 and then March 29: the second calculation starts from February 29. That differs from calculating each date directly from the original January 31 anchor. For a sequence that should land on the last day of every month, or should be calculated from the original anchor, choose the method that matches the intended rule.
If the goal is explicitly “the last day of the next month,” EOMONTH is a more direct function. With January 15, 2024, in A2, enter this formula in B2. It returns February 29, 2024:
=EOMONTH(A2,1)
EDATE and EOMONTH are not interchangeable. EDATE aims for the corresponding day number where possible, while EOMONTH always returns a month’s final day. For instance, from a January 15 starting date, EDATE with one month returns February 15; EOMONTH with one month returns February 29 in 2024.
Entering dates and displaying the result
Excel stores dates as serial values and applies a number format to show them as calendar dates. As a result, an EDATE formula can calculate correctly but appear as a number such as 45321 if the result cell is formatted as General or Number. That number is the underlying date serial, not necessarily a failed calculation.
To display the result as a date in Excel for Microsoft 365 on Windows, select the result cell, then choose Home > Number Format > Short Date. You can also press Ctrl+1 to open Format Cells and choose a date format. On Mac, Command+1 opens Format Cells; in Excel for the web, use Home > Number Format and choose a date option. The exact date appearance can vary with regional settings, such as day-month-year versus month-day-year.
For a formula that needs to construct a starting date without relying on how a typed date is interpreted, DATE is a dependable option. With the result in B2, enter this formula. It creates January 31, 2024, then advances one month, returning February 29, 2024:
=EDATE(DATE(2024,1,31),1)
For dates typed directly into cells, check that Excel recognizes the entry as a date rather than text. A date that looks correct on screen may still be text if it was imported or entered in a format Excel does not recognize under the computer’s regional settings. If EDATE returns an error for such a value, convert the source to a real date value before using it.
Common errors and how to fix them
If EDATE returns #VALUE!, check that the start-date argument is a valid Excel date and that the month argument is numeric. Text imported from another system is a frequent cause: it may look like a date but not be stored as one. Test the source cell’s format and, where needed, convert the imported value to a proper date before calculating.
If the output is a serial number instead of a readable date, change the result cell’s number format to Short Date or another date format. Formatting changes how the value is displayed, not the date EDATE calculated.
If the date is unexpectedly at the end of a month, check whether the target month has the original day number. February cannot contain the 30th or 31st, so EDATE uses February’s final date instead. If your rule is to always choose the last day of a month, use EOMONTH rather than trying to make EDATE behave differently.
If the result is off by a month, inspect the month argument and confirm whether it should be positive or negative. Also check whether a referenced cell contains a formula or value different from what its display suggests. EDATE truncates a fractional month argument to a whole number, so a value such as 1.9 is treated as one month rather than as one month plus part of another month.
A result outside Excel’s supported date range can produce #NUM!. Very large positive or negative offsets are a likely cause. Reduce the offset or check that the starting date and intended result are within the date range your workbook can represent.
EDATE compared with other date calculations
Choose a date function according to the rule you want, not just because two formulas can sometimes return the same answer. EDATE is for shifting by whole calendar months while retaining the day number when possible. EOMONTH is for finding the final day of a month at a chosen offset. Adding a number directly to a date is for moving by days: for example, adding 30 means 30 days, not one calendar month.
That difference makes EDATE a better fit for “same day next month” schedules, while EOMONTH is usually clearer for “month-end” schedules. If you need a date exactly 90 days later, add 90 days rather than using EDATE with three months; calendar months have different lengths. If a schedule must skip weekends or holidays, EDATE alone does not apply those rules, so additional date logic is needed.
Frequently Asked Questions
How do I add one month to a date in Excel?
Put the starting date in a cell such as A2, then use =EDATE(A2,1) in the result cell. Format the result as a date if Excel displays a serial number.
How do I subtract months with EDATE?
Use a negative number for the month argument. For example, =EDATE(A2,-6) returns the date six calendar months before the date in A2, subject to month-end adjustment.
Why does EDATE return the last day of February?
If the starting day does not exist in the target month, Excel returns that month’s final valid day. A January 31 date moved one month lands on February 28 or 29, depending on the year.
Can EDATE calculate the last day of every month?
EDATE is not the clearest choice when the requirement is always month-end. Use EOMONTH with the appropriate offset, because that function returns the last day of the month regardless of the starting day.
Why does EDATE show a number instead of a date?
Excel is likely displaying the underlying date serial because the result cell is formatted as General or Number. Apply a date format through Home > Number Format > Short Date, or use Format Cells to choose another date display.
Does EDATE work in Excel for Mac and the web?
Yes. The EDATE formula works in current Excel for Windows, Mac, and the web. The formula syntax is the same; ribbon layout and date display options can vary slightly between those versions.
Related
- Task List Template Excel Spreadsheet: Set Up and Use
- Tax Audit Form 3CD in Excel: Format and Filing Guide
- Supplier Contact List Template in Excel: Fields and Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Words in Excel: Formulas for Exact and Partial MatchesTo count every occurrence of a word across text in Excel, put the word to find in D1…
- Excel CONCATENATE Formula: How to Join TextIf A2 contains Jane and B2 contains Doe, use =CONCATENATE(A2," ",B2) to join them with a space, producing…
- Capitalize Names in Excel: Formulas and MethodsTo capitalize names in an Excel column, enter =PROPER(A2) in the next column, replacing A2 with the first…
- How to Lock Cells in Excel Formulas With the F4 ShortcutTo lock a cell reference in an Excel formula, edit the formula, select the reference, and press F4.…
- Excel Formula for Average Time Between Dates in DaysIf your dates are in chronological order in A2:A10, use =IF(COUNT(A2:A10)<2,"",(MAX(A2:A10)-MIN(A2:A10))/(COUNT(A2:A10)-1)) to calculate the average gap in days.…
- Excel Filter Shortcut: How to Filter Data Quickly in ExcelIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filters on or off for a selected…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.