EDATE Not Working in Excel? Fix Common Errors

If EDATE is not working in Excel, first check that its start date is a real Excel date, that the formula is entered correctly, and that the result cell is formatted as a date. In Microsoft 365 for Windows, a basic formula is =EDATE(A2,1), assuming A2 contains a valid date; it returns the date one month after A2.

What EDATE Does—and What It Does Not Do

EDATE shifts a date by a specified number of whole months. Its syntax is =EDATE(start_date,months). The months argument can be positive to move forward or negative to move backward. For example, 3 means three months later and -2 means two months earlier. EDATE is useful for recurring schedules, subscription dates, follow-up dates, and maturity dates where the interval is measured in months rather than a fixed number of days.

EDATE does not add a set number of days, and it does not guarantee that the result will have the same day number in every month. If the target month is shorter than the start month, Excel returns the last valid day of the target month. With a start date of January 31, 2024, adding one month produces February 29, 2024, because 2024 is a leap year. Adding one month to January 31, 2023 produces February 28, 2023.

This is different from adding 30 days or using a formula that moves to the same day number where possible. It is also important when calculating repeated monthly dates: if each new date is based on the previous result, a month-end date can shift to the 28th or 29th and stay there in later months.

Check the Formula and Its Cell References

Begin by checking the formula itself. EDATE needs a start date and a number of months, separated by a comma in the formula syntax used in this guide. Do not put the months argument in quotation marks, and make sure the parentheses are balanced. For example, assuming the starting date is in A2 and the number of months to add is in B2, use =EDATE(A2,B2). If B2 contains -1, the result is one month before the date in A2.

Check that A2 and B2 contain the values you expect, rather than blanks, labels, or values from the wrong row. A blank months cell is treated like zero in many formula contexts, so the result may appear to be unchanged. A reference to a text label such as Next month will not work as a numeric months argument. If you are copying a formula down a list, also check whether relative references have shifted as intended.

For a quick test independent of other cells, assuming you want to test January 31, 2024 directly, use =EDATE(DATE(2024,1,31),1). The expected result is February 29, 2024. DATE constructs a real date from year, month, and day numbers, so this can help determine whether the problem is with EDATE itself or with the contents of a referenced cell.

Fix Dates Stored as Text

A cell can look like a date but contain text instead of an Excel date value. This often happens when dates are imported from a website, CSV file, database, or another spreadsheet, or when they are entered in a format that does not match the computer’s regional settings. A date aligned differently from nearby dates can be a clue, but alignment is not a definitive test because formatting can change it.

Assuming the suspected date is in A2, test it with =ISNUMBER(A2). TRUE indicates that Excel sees a number, which is how ordinary dates are stored. FALSE indicates that the value is not numeric and may be text or blank. Note that Excel dates are serial numbers, so a number can be a valid date value even if it currently displays as a plain number.

To correct text dates, select the affected cells and try Data > Text to Columns, then choose the appropriate date order on the final step if Excel offers that option. This can convert recognizable date text into date values. For a small number of cells, you can also edit the entry and confirm it in a date format Excel recognizes. Avoid replacing dates with a guessed interpretation: 03/04/2025 can mean March 4 or April 3 depending on the date convention used by the source.

If you are building a formula and know the year, month, and day separately, use DATE to create the starting value. Assuming year, month, and day numbers are in A2, B2, and C2 respectively, use =EDATE(DATE(A2,B2,C2),1). This avoids relying on Excel to interpret a date string, though the three input cells still need to contain the intended numbers.

When the Result Looks Like a Number

EDATE returns an Excel date serial number. If the result appears as a number such as 45352 instead of a calendar date, the calculation may be correct and only the display format is wrong. Select the result cell, then choose Home > Number Format > Short Date. You can select Long Date or use Format Cells to apply a custom display format if you need a different appearance.

Changing the display format does not change the underlying date or the calculation. The same date can be displayed as 3/1/2024, 1-Mar-2024, or a serial number. If the cell instead shows a string of hash marks, widen the column first; Excel may be unable to display the formatted value at the current width. Hash marks can also appear when a date calculation produces a value Excel cannot display as a valid date, so check the formula and date range if widening the column does not help.

Understand Common Error Messages

The error itself often points to the cause:

#VALUE! usually means the start date or months argument is not usable as a value. Check for text dates, text in the months cell, or a formula that returns text.
#NUM! can occur when the requested result is outside Excel’s supported date range. Check for an unusually large positive or negative months value and for a start date near the limits of that range.
#NAME? can indicate that Excel does not recognize the function name. Confirm that it is spelled EDATE and that the entry begins with an equals sign. If the workbook is being opened in a different spreadsheet program, verify that program’s function support and syntax.
A formula displayed literally in the cell may have been entered as text. Remove a leading apostrophe if present, change the cell format from Text to General, then re-enter the formula. Changing the format alone may not make Excel recalculate an entry already stored as text.

EDATE is a standard worksheet function in current Microsoft 365 Excel, so it does not need to be enabled through the Analysis ToolPak. If the formula is valid but its displayed result does not update after an input changes, check Formulas > Calculation Options and select Automatic. In Manual calculation mode, Excel may wait for a recalculation; press F9 to recalculate the workbook on Windows. On Mac, use the Formulas tab’s calculation controls or the available recalculation command for that Excel version.

Check Separators, Locale, and Workbook Settings

Excel’s formula argument separator can depend on regional settings. The examples here use commas, as in =EDATE(A2,1). If Excel rejects that formula with a syntax warning, your installation may use semicolons as the list separator. In that case, enter the formula using the separator Excel expects. This is a regional setting difference, not a different EDATE calculation.

Date interpretation can also vary by locale. A date typed as 4/5/2025 may be interpreted differently in systems that use month/day/year and day/month/year conventions. For dependable formulas, use a cell already verified as a date value or construct the date with DATE and separate year, month, and day values. When sharing a workbook, consider documenting the date convention used in imported data.

Excel workbooks can use either the 1900 or 1904 date system. This setting affects how stored date serials correspond to calendar dates and can matter when dates are copied between workbooks. Most users do not need to change it to fix EDATE. If copied dates differ by several years between workbooks, compare the date-system setting under File > Options > Advanced on Windows, in the section for calculating this workbook. Do not change it casually, because doing so can shift displayed dates throughout the workbook.

Month-End Dates and Repeated Schedules

A common surprise is that EDATE preserves the day number only when that day exists in the destination month. Assuming A2 contains January 31, 2023, =EDATE(A2,1) returns February 28, 2023. If A2 contains January 31, 2024, the same formula returns February 29, 2024. These are valid results, not errors.

There is a further distinction between calculating each date from the original start date and calculating each date from the previous one. Assuming A2 contains January 31, 2023, =EDATE(A2,2) returns March 31, 2023. But if B2 contains =EDATE(A2,1) and C2 contains =EDATE(B2,1), C2 returns March 28, 2023: February 28 is now the starting date for the second step. For a schedule intended to stay anchored to the original month-end, calculate every row from the original start date and an increasing month count, rather than chaining from the prior result.

For example, assuming the original date is in A2 and the month number 1, 2, 3, and so on is in B2:B13, enter =EDATE($A$2,B2) in C2 and fill it down. The dollar signs keep the original start date fixed while the months reference changes by row. If the intended rule is always the last day of each month, that is a different scheduling requirement; state it explicitly and choose a formula designed for month-end dates rather than assuming EDATE will preserve the last-day status after every step.

EDATE Compared with Adding Days or Using EOMONTH

Choose the function that matches the rule you actually need. EDATE advances by calendar months. Adding 30 days advances by a fixed number of days, so it will not consistently land on the same day of the month. EOMONTH returns the last day of a month a specified number of months before or after a date. For a deadline defined as the final day of the month, EOMONTH may fit better than EDATE.

Assuming A2 contains a valid date and you need the last day of the current month, use =EOMONTH(A2,0). For the last day of the next month, use =EOMONTH(A2,1). By contrast, assuming the same date is in A2, =EDATE(A2,1) aims for the corresponding day number in the next month, or the last available day if that number does not exist. Neither function is universally better; the right choice depends on the schedule’s definition.

Frequently Asked Questions

Why does EDATE show a five-digit number? EDATE returns a date serial number, and the cell may be formatted as General or Number. Apply a date format through Home > Number Format > Short Date to display it as a calendar date.

Why does EDATE return #VALUE! when my date looks correct? The cell may contain text that resembles a date, or the months argument may be text. Test the start date with ISNUMBER, convert imported text dates where appropriate, and confirm that the months value is numeric.

Does EDATE always return the same day of the next month? It returns the corresponding day when that day exists. If it does not, Excel uses the last day of the target month; January 31 shifted one month becomes February 28 or 29.

Why does a monthly series drift from the 31st to the 28th? A formula based on the previous result uses February 28 as the next starting date. To anchor each result to the original date, calculate each row from that original date with a changing month count.

Does EDATE work in Excel for Mac and Excel for the web? EDATE is available in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon locations and calculation controls can differ, and formula separators may follow regional settings, but the function’s purpose is the same.

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.