EDATE Formula Not Working? Fix Excel Date Errors

If EDATE is not working, first check that the start date is a real Excel date and that the month offset is numeric. With a valid date in A2 and a whole-number month offset in B2, enter =EDATE(A2,B2). If the result looks like a number, format the result cell as a date; if Excel shows an error, use the checks below to identify the cause.

What EDATE does in Excel

EDATE moves a date forward or backward by a specified number of calendar months. It is useful for recurring schedules such as monthly reviews, payment dates, subscription renewals, or project milestones. Unlike adding 30 days, it follows the calendar, so the result accounts for months of different lengths and for leap years.

For example, if A2 contains the actual date March 15, 2025, and B2 contains 1, the formula above returns April 15, 2025. If B2 is -3, it returns December 15, 2024. EDATE returns a date value that Excel stores as a number; the cell’s number format controls whether that value appears as a date or as a serial number.

EDATE is built into Excel for Microsoft 365 on Windows. You do not need to enable the Analysis ToolPak. In very old Excel versions, the function may not be available unless the relevant add-in is enabled, but that should not be necessary in current Microsoft 365.

Check the formula and its cell references

EDATE needs two arguments in this order: a start date and a number of months. A misspelled function name, a missing parenthesis, or a reference to the wrong cell can stop the formula from working or produce an unexpected date. Confirm that the date reference points to the intended starting date and that the month reference contains the intended offset.

If you are entering the formula directly, check that it begins with an equals sign and uses the separator your Excel installation expects. The formula examples here use commas. Some regional settings use semicolons between arguments; if Excel rejects a correctly written comma-separated formula, try the separator used by other formulas in your workbook. Do not replace the comma inside a date value or change the order of the EDATE arguments.

Excel may also treat a formula as plain text if the cell was formatted as Text before you typed it, or if an apostrophe or space appears before the equals sign. Change the cell format to General, then edit and re-enter the formula. Simply changing the format after the fact may not convert text that is already in the cell.

Make sure the start date is a real date

A date that looks correct on screen can still be text. This often happens after importing a CSV file, copying values from a website, or receiving dates in a format Excel does not recognize under the computer’s regional settings. EDATE needs a date value it can interpret, not just characters that resemble a date.

Click the start-date cell and inspect the formula bar. If the value is left-aligned, has a leading apostrophe, or does not respond to date formatting, it may be stored as text. Alignment alone is not definitive because users can change alignment manually, so test the value by changing its number format to General. A genuine Excel date displays as a number; a text date remains text.

If A2 contains date text that DATEVALUE can interpret under your regional settings, and you want the next month, use =EDATE(DATEVALUE(A2),1). For example, if A2 contains recognizable text for March 15, 2025, the result is April 15, 2025. DATEVALUE depends on the date format and locale, so a string such as 03/04/2025 can be ambiguous. For a column of imported dates, Data > Text to Columns can be a better conversion route: select the correct date order on the final step before choosing Finish.

If conversion is uncertain, correct the source data or import settings rather than forcing a formula to interpret ambiguous text. A date stored as day/month/year can be read differently on a system expecting month/day/year. Confirm the intended order before converting a whole column.

Fix results displayed as numbers or as formulas

A result such as 45762 does not necessarily mean EDATE failed. Excel stores dates as serial numbers, and a cell formatted as General or Number shows the underlying value. Select the result cell, then choose Home > Number Format > Short Date. You can use a custom date format if you need a particular display, such as a month and year, without changing the calculated date.

If the cell shows the formula text instead of its result, check for Text formatting or a leading apostrophe first. Another possibility is that Show Formulas is on, which displays formulas across the worksheet. On Windows, press Ctrl+` to toggle Show Formulas; the grave accent key is commonly beside the number 1. On Mac, the shortcut can depend on the keyboard layout, so use the Show Formulas command on the Formulas tab if the shortcut differs. In Excel for the web, check the Formulas tab for the equivalent display setting.

When formulas show old results rather than updating after you change an input, check calculation mode. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. In Excel for Mac, calculation settings are available through Excel > Settings (or Preferences) > Calculation. A manual calculation setting can delay updates until you recalculate. Excel for the web generally recalculates formulas automatically.

Understand month-end results

EDATE tries to keep the same day number in the target month. When that day does not exist, it returns the last valid day of the target month. With January 31, 2025, in A2 and 1 in B2, =EDATE(A2,B2) returns February 28, 2025. With 2 in B2, it returns March 31, 2025. The leap year changes the February result: January 31, 2024 plus one month lands on February 29.

This behavior can surprise people who expect EDATE always to preserve the last day of a month. It does not necessarily do that. If A2 contains April 30, 2025, and B2 is 1, EDATE returns May 30, not May 31, because May has a 30th day. For schedules that must always fall on the last calendar day of the target month, use EOMONTH instead. With a real date in A2 and a month offset in B2, =EOMONTH(A2,B2) returns the last day of the month that many months away.

Choose based on the rule you need: EDATE is for the same day number where possible, while EOMONTH is for the end of the target month. This distinction matters for month-end reporting and billing schedules. It is also why adding a fixed number of days is not a reliable substitute for moving by a calendar month.

Diagnose common errors

The error or result often points toward the cause. Check the inputs and the worksheet before replacing the formula with a more complicated one.

#VALUE! commonly means the start date or month argument is not a usable number. Check for text dates, spaces, or nonnumeric values in the referenced cells.
#NUM! can appear when the calculated date falls outside Excel’s supported date range. Check unusually large month offsets and dates near the limits of the range.
#NAME? usually indicates a misspelled function name or text entered where Excel expects a function. In current Microsoft 365, EDATE is a standard function.
A number instead of a date usually means the output cell needs date formatting, not a different calculation.
The wrong month or day often comes from a text date interpreted in the wrong regional order, an incorrect reference, or an unexpected month-end rule.

Also inspect the month argument. EDATE expects a numeric offset; positive numbers move forward and negative numbers move backward. If a month count comes from an imported column, verify that it is numeric rather than text. Fractional month values are truncated to an integer, so use a whole number when you need a clear, predictable offset.

Use EDATE in a recurring schedule

For a schedule where each row is one month after the previous row, put the first genuine date in A2. In A3, with the prior scheduled date in A2, enter =EDATE(A2,1) and fill the formula down. Each row then uses the previous row’s date, which can make a month-end series behave differently from calculating every date from the original starting point.

For example, starting on January 31, 2025, the next row is February 28. A formula in the following row based on that February date returns March 28, because EDATE now starts from the 28th. If the schedule must remain at month-end, use EOMONTH for each row instead. With the prior date in A2, enter =EOMONTH(A2,1) in A3 and fill down; it returns February 28 and then March 31. Choose the method that matches the schedule’s rule, not merely the first result that looks right.

Check workbook date systems and compatibility

Excel workbooks can use different date systems, including the 1900 and 1904 systems. This is most likely to matter when dates are copied between workbooks created on different platforms or with different settings. A date may appear shifted after copying numeric date values, even though the EDATE formula itself is correct. If a whole group of dates differs by the same amount, check the workbook’s date-system setting and how the values were transferred.

When sharing a workbook, use actual date values and clear date formats rather than relying on text that only looks like a date. If a recipient reports a different result, compare the input cell’s underlying value, the workbook’s date system, and the regional interpretation of imported text. Those checks distinguish a data issue from a formula issue.

Frequently Asked Questions

Why does EDATE return a number instead of a date?
The formula likely returned a valid date serial, but the cell is formatted as General or Number. Select the result and choose Home > Number Format > Short Date.

Does EDATE count days or calendar months?
It moves by calendar months. One month after March 15 is April 15, not a fixed number of days after March 15. The number of days in each month can vary.

Why does EDATE from January 31 return February 28?
February has no 31st day, so EDATE uses February’s last valid day. In a leap year, the result is February 29.

How do I make EDATE always return the last day of the month?
Use EOMONTH. It is designed to return the last calendar day of a month, whereas EDATE aims to preserve the starting day number when possible.

Why does EDATE show #VALUE! when my date looks valid?
The starting value may be text rather than an Excel date, or the month offset may be text or otherwise unusable. Convert the date using the correct regional date order and confirm the month value is numeric.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.