I'm hoping someone with knowledge of Excel's inner workings can shed light on a strange Excel issue. I helped someone with an Excel problem, and the solution involved using the TEXT function to format a date within a display string. For reference, the question is here and there is discussion and images moved to chat on the answer. The chat includes a link to a copy of the file.
It was a straightforward use of the TEXT function, that worked everywhere but the OP's worksheet on the OP's computers (the file had been saved from Excel 2016 as V95-2003 .xls). I was able to make it work on my own system in a copy of the worksheet with LO Calc, although I had to jump through hoops to do it (the spreadsheet, itself, behaved squirrelly, in addition to the OP's Excel app).
- The original cell had a "working" formula, just a concatenation of values from various other cells. One of the component referenced cells contained a date, which displayed within the string in raw form. The solution was to wrap that cell reference with a TEXT function to format it as a date.
- The original formula stopped being recognized as a formula if it was edited to insert the TEXT function. The whole formula became treated as a text string, in some cases with a generic error message.
- Preformatting the cell as General didn't help.
- Neither did any kind of editing of the resident formula.
- We ruled out leading blank spaces or single quote, regional list separator issues, and the like (the original formula worked, and none of those things were changed).
- Copying and pasting an edited version of the formula from another cell worked on my system, but not the OP's.
- We eliminated possibilities such as a corrupted worksheet (opened a new worksheet), or use of a file format that predated the TEXT function (resaved the file under a current format and reopened it).
- Restarting the computer didn't affect the issue (not a hiccup).
- This does not appear to be corruption of the Excel Installation. The OP reported that they replicated the problem on a number of computers to which they had access.
- I can't rule out the possibility that all of the Excel installations the OP tested were similarly configured with settings intended to ensure compatibility with ancient Excel installations in the office that can't or won't be upgraded.
TL;DR:
Finally, I had the OP open a fresh worksheet, stick a date in one cell, and then reference it inside a simple TEXT function in another cell. Their Excel 2016 would not accept the TEXT function when typed into the cell (it produced a generic "there is a problem with this formula" error message).
However, they could use the function wizard to have Excel create the formula, and that worked. The resulting formula was exactly the same as what they manually entered.
My question: is there a known problem condition or situation that can cause a function to not be recognized when manually entered, but work when entered using the function wizard? i.e., is this pattern diagnostic of a specific underlying issue?
Does the function wizard force some form of mode, setting or action beyond simply editing the cell content that could result in the function being recognized when it otherwise is not?