EDATE Function Not Working in Excel? Common Fixes

If EDATE is not working in Excel, first check that its start date is a real Excel date and that its months argument is a number; then check the result cell’s number format. With a date in A2 and the number of months to add in B2, the basic formula is =EDATE(A2,B2). In Microsoft 365 for Windows, EDATE is built in and does not require an add-in.

What EDATE Does—and What It Does Not Do

EDATE moves a date forward or backward by a specified number of months. The syntax is EDATE(start_date, months): the first argument is the starting date, and the second is the number of months to move. Use a positive number to move forward and a negative number to move backward. For example, moving a date three months earlier means using -3 for the months argument.

EDATE returns an Excel date value, which is stored as a serial number, rather than a sentence or a month name. A cell displaying a number after you enter the function may therefore contain a valid result that simply has a General or Number format. Formatting changes how the value appears; it does not change the underlying date or the calculation.

EDATE is useful for recurring dates such as monthly reviews, subscription renewals, and payment schedules. It is not a working-day calculator: adding one month does not skip weekends or holidays. It also does not mean “add 30 days.” Calendar months have different lengths, so a one-month shift and a 30-day shift can land on different dates.

Check the Formula and Its Arguments

Start with the simplest possible test. If A2 contains a date and B2 contains a whole number of months, enter =EDATE(A2,B2) in another cell. Check that the parentheses are balanced, the function is spelled EDATE, and the arguments are in the correct order. A reversed formula may be invalid or produce an unintended result.

For a controlled test, use a date made with DATE rather than a date typed in a regional format. If the formula cell is C2, and you want to test January 31, 2024 plus one month, enter =EDATE(DATE(2024,1,31),1) in C2. The result is February 29, 2024, because 2024 is a leap year. If this test works but your original formula does not, the problem is probably in the contents or formatting of the referenced cells.

Excel may use semicolons instead of commas as formula separators under some regional settings. The formulas here use commas, as in standard English-language Excel syntax. If Excel rejects a correctly written formula, check the separator expected by your installation rather than changing the function name. Date entry and date display can also depend on regional settings, so a string such as 4/5/2025 may be interpreted differently in different locales.

Fix Dates Stored as Text

A date that looks right on screen is not necessarily a date value. Imported data, copied text, or a date entered in an unrecognized format can remain text. EDATE may then return #VALUE! because it cannot use the text as a valid start date. Applying a date format to text alone does not convert it into a usable date.

To diagnose the input, click the start-date cell and look at the formula bar and the way the value behaves when you change its number format. A genuine Excel date typically displays as a serial number if you temporarily choose General format; a text date usually stays as text. Alignment is only a clue, since users can change alignment manually. If the date came from a system export, check how that system represents dates and whether the imported column was converted.

For a date you enter directly in a formula, DATE is a reliable way to specify year, month, and day separately.
For a column of imported dates, use Excel’s data-conversion tools or correct the source format, then verify that the result is recognized as a date before relying on EDATE.
Do not assume that changing the cell to Short Date fixes text. Confirm that the value has actually become a date serial.

If you use DATE with separate year, month, and day values, Excel can normalize out-of-range month or day values instead of treating them as text. That can be useful in deliberate date calculations, but it can also conceal a mistaken input. Confirm the intended year, month, and day before interpreting a result.

Understand Month-End Results

One common surprise is that EDATE does not preserve a date that cannot exist in the destination month. If a date falls on the 31st and the next month has fewer days, the result is the last valid day of that shorter month. For example, January 31 plus one month becomes February 28 in a non-leap year or February 29 in a leap year.

This behavior can affect a series of monthly dates. If A2 contains January 31, 2024, and you use the date in A2 to calculate one month ahead in B2, then use the date in B2 to calculate the following month in C2, the sequence becomes February 29 and then March 29. The second calculation starts from February 29; EDATE does not remember that the original date was the 31st.

For a schedule that must always land on the final day of each month, use a month-end function such as EOMONTH instead of expecting EDATE to preserve month-end status. For a schedule intended to use a particular numbered day whenever possible, EDATE’s adjustment for shorter months may be appropriate. Choose based on the rule the schedule is meant to follow, not just on which output looks closest for one example.

Interpret Common Errors and Unexpected Results

#VALUE! commonly points to an invalid start date or an argument Excel cannot interpret as a number. Check for dates stored as text, stray spaces, and text in the months cell.
#NUM! can appear when the calculation produces a date outside Excel’s supported date range. Review unusually large positive or negative month values and the starting date.
#NAME? usually means Excel does not recognize something in the formula. Check for a misspelling, missing punctuation, or a function name that has been entered as text. EDATE is available in current Microsoft 365 Excel.
A number instead of a date usually means the result cell is showing the date serial. Apply a date number format to display it as a calendar date.
A date that is a month off may be caused by an incorrect starting date, a text date interpreted under different regional settings, or an incorrect months value. Inspect the source cells rather than compensating blindly in the formula.

To change how a valid result appears, select the result cell and choose Home > Number Format > Short Date, or open Format Cells with Ctrl+1 on Windows. On Mac, use Command+1 to open Format Cells; ribbon labels and available date formats can vary slightly by version and region. A custom format can also display a longer date, but it does not alter the value used in later calculations.

Check Calculation and Workbook Settings

If the inputs change but the EDATE result does not, check whether calculation is set to Automatic. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options > Automatic. Excel for Mac also has calculation options on the Formulas tab, although the precise ribbon presentation can differ. With Manual calculation selected, formulas may not update until a recalculation is requested.

Press F9 on Windows to recalculate formulas in open workbooks. On Mac, the recalculation shortcut can depend on the keyboard and Excel version; use the Formulas tab’s calculation controls if the function key does not work. EDATE itself is not a reason to assume that recalculation is automatic when the workbook is set to Manual.

Workbooks can also use different date systems. Excel’s 1900 and 1904 date systems assign different serial values to the same calendar date, so copying date serials between workbooks with different systems can lead to a displayed shift. If a problem appears after moving data between files, check the workbook date-system settings and verify the actual displayed dates. This is a workbook compatibility issue, not a special rule that changes EDATE’s month arithmetic.

Choose EDATE, EOMONTH, or Another Method

Use EDATE when the required result is the same day of a later or earlier month where that day exists, with adjustment at the end of shorter months. Use EOMONTH when the required result is specifically the last calendar day of a month. Use WORKDAY when the task is to move by a number of working days and skip weekends, with optional holiday dates. Those functions answer different questions, so substituting one for another can create a schedule that looks plausible but follows the wrong rule.

For example, a monthly review set for the 15th is a natural EDATE task. A month-end report is better described by EOMONTH. A deadline that must avoid Saturday and Sunday calls for WORKDAY rather than EDATE. If the required day is a business day and must also follow a holiday calendar, check the applicable working-day function and supply the relevant holiday dates.

Also decide whether each new date should be calculated from the original date or from the previous result. For a long schedule, calculating each period from the original start date can avoid carrying a shortened February date into later months; calculating from the preceding period deliberately follows the preceding result. The right approach depends on the schedule’s rules. Make the choice explicit and test dates that cross February and months with 30 days.

A Practical Troubleshooting Order

Use this sequence to isolate the cause rather than rewriting a working formula:

Confirm the formula says EDATE and that the start-date and months arguments are in the right order.
Check that the start date is a real date value, not merely text that looks like a date.
Check that the months argument is numeric and has the intended sign and magnitude.
Test with a known date created by DATE and a small month value.
If the answer is a serial number, format the result as a date; do not change the arithmetic to compensate for display.
If the answer is stale, check calculation mode. If it is off by a month or day, inspect the inputs and the intended month-end rule.

A useful final check is to compare a few hand-verified dates, including a month-end date and a date that crosses February. This catches assumptions that a formula can execute correctly while still failing to match the business rule. Keep the original input visible during testing so that a display issue is not mistaken for a calculation error.

Frequently Asked Questions

Does EDATE need the Analysis ToolPak in Microsoft 365?
No. EDATE is available as a built-in function in current Excel for Microsoft 365 and does not need the Analysis ToolPak enabled.

Why does EDATE show a five-digit number?
Excel stores dates as serial values. The calculation may be correct, but the result cell is probably formatted as General or Number. Apply a date format to display a calendar date.

Why does EDATE return #VALUE?
The start date may be text or invalid, or the months argument may contain text Excel cannot interpret as a number. Check the source cells and test with a recognized date and numeric month count.

Does EDATE always keep the same day of the month?
It keeps the day when that day exists in the destination month. If it does not, the result is adjusted to the last valid day of that month. A later calculation from that result uses the adjusted date.

Can EDATE skip weekends and holidays?
No. EDATE moves by calendar months and does not account for working days. Use a working-day function such as WORKDAY when the schedule needs to skip weekends or specified holidays.

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.