1

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?

Improve this question
8
  • I've Excel 2013, but have never met this problem; also it works fine in online Excel. Can be a bug in 2016, but I don't think so. If a pc restart doesn't help I'd suggest reinstall Excel. Are you sure OP has tried to enter the formula with the correct list separator, it's a language dependent character and it's a common issue to miss it.
    – Máté Juhász
    Commented Apr 16, 2018 at 20:46
  • @MátéJuhász, I verified that what they entered was correct. The solution involved a minor edit to existing formulas. I'm wondering if they did something strange like set the default format to V95-2003 for compatibility with their legacy spreadsheets, or some such thing. There were also macros active, although they were blocked by LO Calc. I didn't explore that, but they could be doing something squirrelly there.
    – fixer1234
    Commented Apr 16, 2018 at 20:57
  • It's really strange behaviour, usually this never happens.I would like to raise my point and is, may be the Formula is getting some conflict with delimiter, in some of the countries people use comma instead of Decimal and semicolon for list separator. Other should be any UDF with same Reserve word.
    – Rajesh Sinha
    Commented Apr 17, 2018 at 5:44
  • another, you have written ,"The original cell would not accept the formula (treated the whole formula as a text string if the formula contained the TEXT function).", possibly the cell formatted for Left alignment and the Formula starts with '= ,, just check it.
    – Rajesh Sinha
    Commented Apr 17, 2018 at 5:53
  • @RajeshS, thanks for the suggestions. I had verified and ruled those out. Everything worked without the TEXT function, as well as with it as long as the function wizard inserted the function into the formula. If it was manually inserted exactly the same way, the formula failed. Weird, huh?
    – fixer1234
    Commented Apr 17, 2018 at 7:25

2 Answers 2

Reset to default
-3

Following are the possible reasons the Formula appears in the cell like String.

  1. Check if there is whitespace before = sign of the formula.

    01-01-2018       =TEXT(D8,"dd mmm")

  2. The sheet might be in Formula View mode.

  3. Make sure the format of the cell is set to 'General' rather than 'Text'.

Improve this answer
7
  • Normally, these would all be possibilities. In this strange case, there was an existing formula that worked. It was just a concatenation of contents from various other cells. One of those cells contained a date and the concatenation used the raw stored day count rather than a readable date. The TEXT function was wrapped around that cell reference in the middle of the string to format it. If it was manually edited in, the entire formula failed (displayed formula as text string or produced a generic error message). If the function wizard did the same thing, the formula worked.
    – fixer1234
    Commented Apr 17, 2018 at 7:36
  • Go to the chat under my answer there. The OP posted lots of images plus a link to a copy of the file. I got that copy to work in LO Calc with manual editing, but editing wouldn't work for the OP, only the function wizard worked.
    – fixer1234
    Commented Apr 17, 2018 at 7:39
  • Okay,, thanks,,, its really different and matter to research,, :-)
    – Rajesh Sinha
    Commented Apr 17, 2018 at 7:58
  • @fixer1234,, i've seen the related Question and the attached screen shot also, and my findings is that,,** "Excel never Produces Current date in such format as he has written, when used =TOday(). And in Cell O11, if he got value 15042018 after he has used Concatenate then the Value is just a number is not a Date and since it's a numeric value then the format doesn't supports the TEXT command. It simply need a different approach that is ,, how to convert number into date"**.
    – Rajesh Sinha
    Commented Apr 18, 2018 at 6:56
  • I can provide perfect solution for that But I'm waiting for the confirmation that my perception is correct & he wants the same to solve with.
    – Rajesh Sinha
    Commented Apr 18, 2018 at 7:00
-3

Now the issue has been solved.

My findings on the issue:

  1. Earler the formula cell B10 was fomatted as TEXT.
  2. Formula is reding the current Date fron N12.
  3. Cell N12 wrapped with the TEXT function and originally the format was ddmmyyyy, no delimiter.

Solution I've applied:

  1. Changed the Format of Formula Cell B10 to GENERAL.

  2. Modified the Formula and included the desire format as,Text(N12,"DD/MM/YYYY").

Now Excel is considering the Formula in B10 as a FORMULA not a STRING, and also produced the result in desire Format.

Check the Screen Shot below.

enter image description here

Improve this answer
1
  • 2
    As I explained, this was not the problem and the cell formatting was ruled out. Remember, there was already a working function there; it stopped being recognized as a function when it was manually edited to wrap the TEXT function around the cell reference. Also, as I described, I was similarly able to make it work on my own system, but the OP could not on theirs. This answer does not address the question.
    – fixer1234
    Commented Apr 19, 2018 at 7:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .