SOLVED

Office 365 Excel functions DATEDIF and XLOOKUP not available

Copper Contributor

Hello good day - presently I unable to to see functions DATEDIF and XLOOKUP. Are these Add-ins features or how do I load? Yes, I have correctly uninstalled Office 365 the performed new installation. Thanks- regards.

9 Replies

@trevorzglobaldd 

DATEDIF function (microsoft.com) is practically in any version of Excel, but it is not shown in list of functions (see comment in above support post). Thus simply type in cell =DATEDIF(...

 

XLOOKUP function (microsoft.com) most probably shall be available on all channels for Microsoft 365, includes semi-annual. But I'm not sure about Office 365.

@Sergei Baklan 

I tried typing =DATEDIF(... and the parameters and get a #NAME? Error.

@cjday524 

Please try to open attached file if it works in your environment

image.png

@Sergei Baklan Thank you for your download. It was very helpful in troubleshooting a similar issue I had. My error was #NUM!. Your formula worked, so I Googled #NUM error and found out that the dates have to be in (earlier date, later date) order. Fortunately, all my dates are progressive, but curious if =DATEDIF function can work if dates increase and decrease (resulting in a negative number of months). Again, thank you.

best response confirmed by Hans Vogelaar (MVP)
Solution

@CindyA123 

No, Excel doesn't support negative time and date directly. As workaround that could be

image.png

Use DAX function
=DATEDIFF(Sheet no.[column no],Today(),Year)

@LaymanwalkthroughYT 

How that could help?

Returning result into the grid we need first add the source to the data model, next return as PivotTable or linked-back table using DAX query. Not very straightforward solution.

More important, if we use DATEDIFF in calculated column, it returns an error for entire column if in any row start date is more than end date. Which returns us to the initial question.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@CindyA123 

No, Excel doesn't support negative time and date directly. As workaround that could be

image.png

View solution in original post