To move a date forward one calendar month in Excel, put the original date in A2 and enter =EDATE(A2,1) in another cell. Format the result as a date, then fill the formula down for other rows. EDATE is useful for monthly schedules because it advances by calendar months, rather than adding a fixed number of days.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows. The EDATE function is also available in Excel for Mac and Excel for the web.
- A cell or column containing dates that Excel recognizes as dates, not text that only looks like a date.
- An empty column for the results, so the original dates remain unchanged.
You do not need an add-in for EDATE in current versions of Excel. If you are working with an older workbook or an older Excel release and the function is not recognized, check that version’s available functions before changing the data.
Step-by-Step
- Check the source dates. Put each starting date in its own cell, for example, dates in A2:A20 with a heading in A1. Click a date and look at the formula bar: a genuine Excel date is stored as a number, even though the cell displays a familiar date format. If the value is aligned or imported oddly, that alone does not prove it is text, so test a sample by changing its number format to General. A date will show a serial number; text will remain text.
- Choose a result column. Keep your source dates intact and use a blank column for the moved dates. Add a heading such as “One month later” in B1. This makes it clear which dates are original and which are calculated, and lets you compare the results before using them elsewhere.
- Enter the EDATE formula. With the original date in A2, enter this in B2:
=EDATE(A2,1). The first argument is the starting date, and the second is the number of months to move. A positive 1 means one month forward; a negative number would move backward. For example, if A2 contains January 15, 2025, the result is February 15, 2025. - Confirm the result is displayed as a date. If B2 shows a number such as 45672 instead of a calendar date, the calculation may still be correct; the cell is likely formatted as General or Number. Select B2, open the Home tab, and choose a date style from the Number Format list. You can also open Format Cells and choose a date format. Formatting changes how the value appears, not the underlying date calculation.
- Apply the formula to the other rows. Select B2 and drag the small fill handle at the lower-right corner of the selection down alongside your source dates. Excel adjusts the reference for each row, so B3 calculates from A3, B4 from A4, and so on. You can also copy B2, select the remaining result cells, and paste. Check the last few rows to make sure the formula filled through the full list and did not include unrelated blank rows.
- Check month-end behavior. EDATE keeps the day number when that day exists in the target month. If it does not, Excel returns the last day of that month. For example, a starting date of January 31, 2025 moves to February 28, 2025. In a leap year, January 31, 2024 moves to February 29, 2024. This is expected behavior, not a calculation error.
- Build a consecutive monthly series if needed. If A2 is the first date and B2 is its one-month-later result, you can make each following row advance from the result immediately above it. With B2 already containing the first moved date, enter
=EDATE(B2,1)in B3, then fill B3 down. This creates dates one month apart while applying the same month-end rule at every step. It differs from calculating every row from the original date, which can matter for dates near the end of a month. - Review and save. Compare a few output dates with the starting dates, including a date near the end of a month. If the results look right, save the workbook. If another application needs fixed values rather than formulas, copy the results and use Paste Special to paste values into a separate range; keep a copy of the formula results if you may need to recalculate later.
For a one-off calculation, the number of months can be changed directly in the formula. With the starting date in A2, =EDATE(A2,12) returns the date twelve months later. The function counts calendar months, so twelve months later is generally the same calendar date in the following year, subject to the same rule for dates that do not exist in the target month.
Common Mistakes
- Adding 30 days instead of a month. Months have different lengths, so adding 30 days can land on an unexpected day. EDATE moves by calendar month and preserves the day where possible.
- Assuming every month-end stays at month-end. EDATE uses the original day number when possible. January 30, for instance, moves to February 28 in 2025, then to March 30 if the next calculation uses February 28 as its starting point. If you need every result to be the last day of its month, that is a different requirement and needs a month-end formula rather than EDATE alone.
- Using a date that is actually text. Imported dates can look valid but fail in calculations or produce errors. Convert them to real Excel dates before relying on EDATE; the troubleshooting section below explains how to identify and fix this.
- Overwriting the original dates. Putting the formula over the source data can remove the starting dates or create a circular reference. Use a separate output column unless you have a deliberate reason to replace the originals.
- Reading a serial number as a wrong answer. Excel stores dates as serial values. A result shown as a five-digit number usually needs date formatting, not a different formula.
- Filling the wrong pattern. When continuing a series, make sure each row refers to the preceding calculated date if that is the pattern you want. If every row should instead be based on its corresponding original date, fill a formula that refers to that row’s source cell.
Tips
- Use a clear layout. Put source dates and calculated dates in separate, labeled columns. This makes it easier to audit the results and less likely that someone will mistake a calculated date for an original value.
- Choose the right meaning of “one month later.” EDATE is appropriate when the goal is the same day number in the next month where possible. If the requirement is “the last calendar day of each month,” or “the 15th business day,” clarify that rule before building the schedule.
- Use the months argument for other intervals. The second argument can be any whole-month count, including a negative count. For a quarterly step, use 3; for a year, use 12. A cell containing the month count can also be used as the second argument if you want to change the interval without editing the formula.
- Keep the formula when dates may change. If a source date is updated, an EDATE result recalculates automatically. Pasting values is useful for a final static export, but it removes that link to the source.
- Account for regional date entry. Excel’s interpretation of entries such as 03/04/2025 can differ depending on regional settings. Use an unambiguous date entry or confirm the cell’s date value before calculating.
Troubleshooting
The formula displays as text. If the cell shows the characters =EDATE(A2,1) instead of a result, the cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, re-enter the formula, and press Enter. Also check that Show Formulas is not enabled on the Formulas tab.
Excel returns #VALUE!. Check that the referenced starting value is a valid date rather than text or an invalid date. If the date came from a CSV or another system, select the source cells and use Data > Text to Columns to inspect or convert the values. Choose the correct date order if Excel offers date-format options, then finish the conversion and test EDATE on one row.
Excel returns #NAME?. Check the function spelling and make sure the cell begins with an equals sign. In current Microsoft 365 Excel, EDATE is built in. If you are opening the file in an unusually old spreadsheet program or older Excel version, verify function support there. Do not replace it with a made-up function name.
The result is a number or looks like the wrong date. Select the result and apply a date number format. Then check the source date and the second EDATE argument. A 1 advances one month, while 12 advances twelve months. For an input at month-end, also compare the answer with the target month’s final day.
The output skips rows or stops early. Inspect the first and last formulas in the filled range. Make sure the fill handle reached the final source row and that no blank rows interrupted an automatic fill. You can select the full intended output range and paste the formula down it to avoid missing rows.
Excel for Mac or the web behaves differently. The EDATE formula syntax is the same in Excel for Mac and Excel for the web. Ribbon placement or the appearance of the fill handle can vary slightly by platform, but enter the formula in a cell and copy or fill it down as usual. On Mac, use the Home tab’s number-format controls to format the result; in Excel for the web, use the Home tab’s number-format menu.
Frequently Asked Questions
Can I move a date forward by one month without changing the day?
Use EDATE with a month increment of 1. It retains the day number if that date exists in the next month. If not, it returns that month’s final day, so January 31 can become February 28 or 29.
Why does EDATE turn January 31 into February 28?
February does not have a 31st day. EDATE returns the last valid day in the target month rather than rolling into March. The result is February 29 when the target February is in a leap year.
Does EDATE work in Excel for Mac and Excel for the web?
Yes. The function and its arguments work the same way in current Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The location and appearance of formatting controls may differ slightly.
Can I use EDATE to move dates backward?
Yes. Use a negative number of months as the second argument. For example, with a valid date in A2, =EDATE(A2,-1) returns the date one calendar month earlier, applying the same month-end rule.
How do I make every result the last day of a month?
EDATE by itself does not guarantee that, because it preserves the day number when possible. Use a month-end-specific calculation if that is the schedule rule, and check the formula against a few sample months before filling a full list.
Related
- How to Delete Microsoft Excel Without Losing Files
- How to Delete Multiple Cells at Once in Excel
- How to Delete Leading Zeros in Excel Without Losing Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel OFFSET Function: How It Works and When to Use ItExcel’s OFFSET function returns a reference to a cell or range a chosen number of rows and columns…
- Excel INDIRECT Drop-Down Lists: Setup and FixesTo make a dependent drop-down list in Excel, create a named range for each set of choices, then…
- Excel Not Equal Symbol: How to Use <> in FormulasIn Microsoft Excel, the not-equal operator is <>—a less-than sign followed by a greater-than sign. Use it in…
- Count Unique Values in Excel with a Simple FormulaTo count distinct, nonblank entries in a column in Microsoft 365, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) in an empty cell, replacing…
- Excel Formula for the First 3 Digits: Text or Numbers?To return the first three characters from a value in cell A2, use =LEFT(A2,3). The result is text,…
- Excel Formula List: Essential Functions With ExamplesAn Excel formula list is a practical reference for choosing the right calculation: use =SUM(B2:B6) to total a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.