Advanced Formula Environment - failed to load

[PatrickLeavy]

hi everyone,

I've been seeing this error message in most workbooks recently: "Failed to load. Unable to validate date order test"

Some workbooks work, but most don't.

Anyone know what's going on with this?

[Details: I'm running my office apps via a Business Standard 365 licence and am using the Insider current preview channel.]

[jack-williams]

Thanks for the report @PatrickLeavy.

In a workbook that fails to load AFE, if you write 1/2/3 into a cell, what is the result? Does it get changed to a date?

[PatrickLeavy]

Hi @jack-williams , Thank you for replying ☺️ When I type 1/2/3 in it returns 37653 But then that also happens in workbooks that can load AFE

[jack-williams]

What is the result when writing =TODAY() into a blank cell return? Do you have a particular date format?

[PatrickLeavy]

that returns 44931 in both cases.

My locale is UK for dates. In both workbooks if I apply the format Short Date it converts correctly to 05/01/2023 so I don't think I've got any weird date formatting going on.

[jack-williams]

If I understand correctly, it seems that Excel is not automatically applying date formatting?

In AFE we use a test to detect the locale: we write 1/2/3 into a hidden sheet as an English formula and read back the auto-applied date formatting. If Excel does not apply date formatting, then this is going to cause the problem you see.

I'll look into this more, but it might be that we need to change the date order test to not rely on automatic conversion of 1/2/3 into a date with formatting.

[PatrickLeavy]

interesting... thank you. BTW I'd be happy to tell AFE manually what the locale is, if it comes to that ☺️

I do use a productivity macro workbook that starts up with every workbook in the background. That has custom date formats but they only kick in when used for the first time in the new workbook. What's probably happening is that for workbooks where I've been using the custom formatting already, AFE won't open, but for those where I haven't invoked the macro workbook's custom formatting, it will load fine.

[NWilson]

@PatrickLeavy We have made some guesses about what your productivity macros are doing. It sounds like they are interfering with (or undoing) the way Excel assigns number formats automatically to cells. This is indicated by the fact that you see "44931" when you type "=TODAY()" into a fresh blank cell (rather than the expected date format).

We're sorry that you weren't able to use AFE last week, we just hadn't anticipated/tested this scenario.

AFE uses various tricks like this to try and detect locale settings (because there is no API to access those settings in a taskpane).

I have just done a bugfix release which changes the way AFE detects the date-order locale setting, so that it does not rely on the cell's number formats anymore.

Are you able to confirm that the latest release works for you? Thank you.

[PatrickLeavy]

@NWilson - perfect that has enabled it to load!

That is next-level responsiveness from a dev team - you guys rule!