Formula Errors

How to Fix Excel #NAME? Error in Date Difference Formulas

Amos Gikunda Sep 25, 2026 869 views

Question details

The user encounters a #NAME? error when trying to calculate the number of full years between two dates in Excel.

How to Fix Excel #NAME? Error in Date Difference Formulas

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating the difference in full years between two dates using a formula.
Observed behavior: The formula returns a #NAME? error instead of the expected number of years, typically due to a typo in the function name such as DATEIF instead of DATEDIF.

Before you start

Ensure both date cells contain valid date formats recognized by Excel, rather than plain text strings, before applying the formula.

Solution 1Recommended

Correct the Function Name to DATEDIF

The #NAME? error occurs because Excel does not recognize 'DATEIF'. Changing the function name to the correct 'DATEDIF' resolves the issue.

Excel's DATEDIF function is considered a legacy function, which means it doesn't always appear in the formula autocomplete dropdown, leading users to accidentally guess the spelling as DATEIF. Correcting this typo is the most direct fix.

Select the error cell

Click on the cell displaying the #NAME? error to view its contents in the formula bar.

Identify the typo

Check the formula bar at the top of the worksheet and look for the misspelling 'DATEIF'.

Update the formula

Change 'DATEIF' to 'DATEDIF'. For example, rewrite it as =DATEDIF($B$2,B13,"y").

Apply changes

Press Enter to apply the corrected formula. The cell will now display the number of completed years between the two dates.

Handling Start and End Date Order: If the start date is later than the end date, DATEDIF returns a #NUM! error. You can use an IF statement to dynamically arrange them chronologically: =IF($B$2<B13,DATEDIF($B$2,B13,"y"),DATEDIF(B13,$B$2,"y")).

Solution 2

Calculate Calendar Years Using ABS and YEAR

If you only need the difference in calendar years and want to avoid using the hidden DATEDIF legacy function entirely, you can use the YEAR function combined with ABS.

Powerful Spreadsheet Alternative

Calculate Date Differences Easily with WPS Spreadsheet

WPS Office Spreadsheet fully supports the DATEDIF function and all standard date formulas, allowing you to seamlessly calculate days, months, or years between dates without encountering compatibility issues.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing your dates.
2. Select the output cell: Click on the specific cell where you want the date difference to appear.
3. Enter the formula: Type =DATEDIF(start_date, end_date, "y") and rely on WPS Spreadsheet's smart formula auto-suggest to avoid spelling mistakes.
4. Get instant results: Press Enter to instantly view the calculated completed years without any #NAME? errors.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in function prompts to prevent typos like DATEIF.Lightweight, fast, and free to use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel give a #NAME? error for my date formula?

The #NAME? error occurs when Excel doesn't recognize text within a formula. For date calculations, this usually means a function has been misspelled, such as typing DATEIF instead of the correct DATEDIF.

Does the DATEDIF function round up the years?

No, DATEDIF calculates the number of fully completed years between two dates. For example, if 3 years and 11 months have passed, the formula will return 3, not 4.

Why did my DATEDIF formula return a #NUM! error instead?

The DATEDIF function returns a #NUM! error if the start_date is greater (later in time) than the end_date. To fix this, ensure the older date is entered as the first argument, or wrap the formula in an IF statement to handle either order.

Can I calculate months or days using the DATEDIF function?

Yes. You can change the third argument in the DATEDIF formula from "y" (years) to "m" to return completed months, or "d" to return the total number of days between the two dates.

Excel & Data Problems