"DATEDIF" not available in my version of EXCEL. How to reinstate please.

Question

Trying to use "DATEDIF" function but not available EXCEL.

How can I reinstate this missing function?

Answer 1

Hi Andrew

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

The DATEDIF function is available in any version of Excel, but it is not shown in the list of functions. You can simply type =DATEDIF( in a cell to use it.

The above is as suggested by Sergei Baklan in the thread below

https://techcommunity.microsoft.com/t5/excel/office-365-excel-functions-datedif-and-xlookup-not-available/m-p/3600391

Learn more: https://support.microsoft.com/office/25dba1a4-2812-480b-84dd-8b32a451b35c

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

I type =DATEDIF(N2,V2,"m") and nothing appear.

II looks whether DATEDIF function would not exist

Answer 3

That function is not recognized (get #NUM! error in cell) in this version of excel: Version 2308 Build 16.0.16731.20182

If a function "is not recognized", we get a #NAME error.

If you get a #NUM error, the function is recognized. But you are misusing it.

Answer 4

I type =DATEDIF(N2,V2,"m") and nothing appear. II looks whether DATEDIF function would not exist

What do you mean by "nothing appears"?

If the function "does not exist", normally we would get #NAME error, not "nothing", if none of the other mistakes below apply.

If the cell literally displays =DATEDIF(N2,V2,"m") when the cell is not selected, that usually means the "formula" is treated as text. Typically, that is either because the cell is formatted as Text or because there is an extraneous character before "=".

You might check to see if Excel is in Manual calculation mode. Even if it is, usually Excel still performs the calculation when we type a formula into a single cell. And even when a cell is not calculated due to Manual mode, usually the formula result is zero, not "nothing".

Another possible reason why the DATEDIF formula is not calculated is because there is an error elsewhere in the workbook that aborts the calculation cycle. Typically, that is due to a circular reference. But even then, the formula result is usually zero, not "nothing".

The only time I have seen literally "nothing" in a cell after entering a formula is when the calculations are disabled for the individual worksheet. AFAIK, that can only be done by setting worksheet.EnableCalculation = False in VBA.

You could confirm that by entering "? ActiveSheet.EnableCalculation" without quotes in the VBA Immediate Window (press ctrl+g in VBA).

All of the above mistakes can avoided (for testing purposes) by closing all instances of Excel, then restarting Excel. In the new worksheet, enter the following into A1:

=DATEDIF(DATE(2023,1,1), DATE(2024,1,1), "m")

It should return 12.

If you still see literally "nothing", it might be necessary to open a new instance of Excel in "safe mode" and perform the test in A1. Google "excel safe mode" without quotes for instructions.

Answer 5

Hi,

It is important to note that DATEDIF has been undocumented in all versions except Excel 2000 and now 2021 & 365.

Here is Microsoft's help on this function as of Office 365:

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60399&ui=en-us&rs=en-us&ad=us