What does the two - sign mean at the beginning of a formula?
Ex: This formula gets a string date and converts it into an Excel date serial number:
=--(MID(S2,4,3)&LEFT(S2,3)&MID(S2,7,4))
The formula works fine, I just want to understand the -- in the formula.
@JNevill's Comment seems a good answer to me but for the sake of an Answer consider Y in A1 and Y in B1. This formula:
=(A1=B1)
returns TRUE. As mentioned, stick an operator in front (with 0+, or 1*) and the Boolean is turned into 1, or 0 if B1 (only) is changed to, say, X.
The single minus negates that. 0-, or -1*, at the front returns -1 for both A1 and B1 equal to Y. Negate that and the -1 result becomes 1, say with =--1*(A1=B1).
But then multiplying by one (or adding zero) is pointless, might as well just go for:
=--(A1=B1)
Note that because of the order in which the evaluations take place, the above is not the same as:
=--A1=B1
which has no meaning so returns #VALUE!.
Text functions (MID and LEFT in your example) return strings, so constructing a date index for today for example (42311 in the 1900 date system) with such functions (alone) returns five characters Excel does not recognise as possibly representing a date. Converted to numeric format and represented as a date this should look like 'today'.
So format a cell as Text and enter 42311, then reformat as Date and the result is still 42311. Format a cell as General and enter:
=--("42"&"311")
before then formatting as Date and you should see something that looks like 'today'.
