Suggestions on how to fix the error of not being able to use the DATEDIF function in Excel

DATEDIF is a function used to calculate time differences. If you can't use the DATEDIF function in Excel, please refer to some workarounds shared by TipsMake!

When wanting to calculate time differences in Excel, the vast majority of users will choose to use the DATEDIF function. However, the process of using this function does not always go smoothly, making calculations difficult. In case you cannot use the DATEDIF function in Excel , below are the tips you should refer to.

Suggestions on how to fix the error of not being able to use the DATEDIF function in Excel Picture 1

Why can't I use the DATEDIF function in Excel?

The DATEDIF function is a foreign function from Lotus 1-2-3. And so, to be able to use this function, we need to do some basic setup. But even though it has been set up, sometimes the process of using the DATEDIF function still encounters some problems. Below are the most common reasons why you cannot use DATEDIF.

1: Excel does not have the DATEDIF function

As mentioned above, DATEDIF is an external function. Therefore, if your Excel is not installed, then you certainly will not be able to use DATEDIF at this time.

2: The start date is after the end date

When using the DATEDIF function and you enter a start date that appears after the end date. At this point, the system will recognize this as an illogical calculation. From there the result will not appear and instead the #NUM error will be reported. For example, calculate the time difference between February 1, 2023 and March 1, 2024. If you enter a start date of March 1, 2024 and an end date of February 1, 2023, the calculation will show an error.

3: The DATEDIF function does not show input suggestions

Normally when you enter a function into Excel, you will see data entry suggestions. However, with the DATEDIF function, this is not the case. But in reality, this is not an error but because it is like that by default.

Suggestions on how to fix the error of not being able to use the DATEDIF function in Excel Picture 3

Remedies when Excel cannot use the DATEDIF function

Excel not being able to use the DATEDIF function will bring a lot of trouble. However, to handle this problem in Excel is quite simple.

1: Add the DATEDIF function to Excel

First, if you notice that Excel does not have the DATEDIF function. Now you can add it to Excel and use it. And the good news for you is that this function will be built into Excel's settings. So you don't need to download or install any files on your computer anymore.

1. B1 : Open Excel on your computer, then click on file and then select options.
2. Step 2 : Select the Add-ins section in the left column > Scroll down to the Manage line , select Excel Add-ins and then press the Go button .
3. Step 3 : Finally, tick the Analysis ToolPak box on the window and select ok to save and you're done.

Suggestions on how to fix the error of not being able to use the DATEDIF function in Excel Picture 5

In my opinion, this is the simplest and most effective way to install the DATEDIF function in Excel 2016 at present. With other versions of Excel, you can do the same!

2: Understand the formula of the DATEDIF function

So that the process of using the DATEDIF function goes smoothly. The most important thing is to clearly understand the formula of this function. And by default this function will not display input suggestions. So memorizing the formula is even more important.

Specifically, the DATEDIF function in Excel will have the formula =DATEDIF(start time, end time, time unit you want to display) .

In there is the time unit you want to display. You can choose from the following units.

1. 'D' is the rounded date unit.
2. 'M' is the rounded month unit.
3. 'Y' is a rounded year unit.
4. 'MD' is a date unit that is not rounded and ignores months and years.
5. 'YM' is a month unit that is not rounded and ignores days and years.
6. 'YD' is a year unit that is not made in and ignores days and months.

Note: With time unit section. You can use lowercase and uppercase letters. In particular, you can absolutely use each format individually or combine all 3 formats (date, month, year) depending on your preference).

Suggestions on how to fix the error of not being able to use the DATEDIF function in Excel Picture 7

And above is some information about the situation of not being able to use the DATEDIF function in Excel that TipsMake has compiled. This article does not have too much content but hopefully it can help you in using DATEDIF in Excel.

David Pac

Update 21 April 2024