Function Problems

Fix Excel for Mac Not Showing the DATEDIF Function

Jason Miller Oct 21, 2026 868 views

Question details

The user is unable to find or use the DATEDIF function in Excel for Mac and needs to restore its functionality.

How to Fix Excel for Mac Not Showing the DATEDIF Function

Product: Excel for Mac
Device & OS: Mac
Scenario: Attempting to calculate the difference between two dates using the DATEDIF function.
Observed behavior: The DATEDIF function does not appear in the autocomplete list, seems to be missing entirely, or returns an error when used.

Before you start

Ensure you have an active Microsoft 365 subscription or a valid Office license, and verify that you know the exact start and end dates you want to calculate.

Solution 1Recommended

Manually Enter the DATEDIF Formula

DATEDIF is a hidden function in Excel for compatibility reasons, meaning it will not appear in the formula drop-down list but will work when typed directly.

Because DATEDIF was originally provided to support older Lotus 1-2-3 workbooks, Microsoft Excel hides it from the standard Formula Builder and autocomplete suggestions. However, it is fully functional when entered manually.

Select the target cell

Click on the cell where you want the calculated date difference to appear.

Type the formula directly

Enter =DATEDIF( into the formula bar. Do not wait for the autocomplete suggestion to appear.

Input the arguments

Type your start date cell reference, end date cell reference, and the unit parameter. For example, enter =DATEDIF(A1,B1,"Y") to calculate the difference in years.

Execute the calculation

Press the Return key to apply the formula and view your result.

Syntax Requirement: Always ensure the start date is earlier than the end date; otherwise, the formula will return a #NUM! error.

Solution 2

Verify License and Update Excel for Mac

An inactive license or an outdated Excel version can cause built-in functions to malfunction or return errors.

Seamless Formula Calculation

Easily Calculate Date Differences with WPS Spreadsheet

If you are struggling with missing or hidden functions in Excel, WPS Office provides a reliable, free alternative. WPS Spreadsheet fully supports the DATEDIF function along with hundreds of other built-in formulas, ensuring your data calculations run smoothly without complex troubleshooting.

1. Install WPS Office: Download and open WPS Office, then create a new Spreadsheet.
2. Enter your dates: Input your start date in cell A1 and your end date in cell B1.
3. Apply the formula: Select cell C1 and type =DATEDIF(A1, B1, "Y") to calculate the difference in years.
4. View the result: Press Enter to instantly view your accurately calculated date difference.

Fully compatible with Microsoft Excel formulas, including the DATEDIF function.Lightweight and fast, optimized perfectly for both Mac and Windows systems.User-friendly interface that makes spreadsheet data analysis intuitive and simple.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't DATEDIF show up in the Excel autocomplete list?

DATEDIF is a legacy function provided primarily to maintain compatibility with older Lotus 1-2-3 workbooks. As a result, Microsoft Excel treats it as a hidden function. It won't appear in the Formula Builder or autocomplete list, but it still works perfectly when you type it manually.

What do the units "Y", "M", and "D" mean in the DATEDIF function?

These are unit parameters determining the output format. "Y" calculates the number of complete years between the two dates, "M" calculates the number of complete months, and "D" calculates the exact number of days.

Why am I getting a #NUM! error when using DATEDIF?

The #NUM! error usually occurs if the start date argument is chronologically later than the end date argument. To fix this, ensure the first date in your formula is earlier than the second date.

Excel & Data Problems