2

I am getting this really bizarre error. In cell A1 I have 21/02/1971 (Brazilian-style date), in A2 I have =today()-A1. I receive the following error:

#VALUE! Error: Function MINUS parameter 2 expects number values. But '21/02/1971' is text and cannot be coerced to a number.

The weird thing is: if I change the date, say, to 22/02/1971, or 21/03/1971, or 21/02/1972 or any combination like that, everything works fine and the formula actually calculates what's supposed to. Any clues? Already tried disabling cookies, cache, restarted browser, tried different browser and computer, all the basic stuff.

2

For lack of detail in the Q this is pure speculation:

Guess #1: There is nothing whatever special about 21/02/1971.
Guess #2: It was imported from a web site.
Guess #3: The cell in question contains a non-printing character.
Guess #4: That character is Unicode, not ASCII – so would not be removed with =CLEAN.
Guess #5: That character is ZWSP.
Guess #6: It is at the end of the date only.

If so, where A1 is the reference to the cell containing that date:

Guess #7:

=left(A1,10)  

might suit, prior to subtraction from =TODAY().

0
  • convert it with TO_DATE()
    =TODAY()-TO_DATE(A1)
    6

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.