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 andY
in B1. This formula:returns TRUE. As mentioned, stick an operator in front (with
0+
, or1*
) and the Boolean is turned into1
, or0
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 toY
. Negate that and the-1
result becomes1
, say with=--1*(A1=B1)
.But then multiplying by one (or adding zero) is pointless, might as well just go for:
Note that because of the order in which the evaluations take place, the above is not the same as:
which has no meaning so returns
#VALUE!
.Text functions (
MID
andLEFT
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:before then formatting as Date and you should see something that looks like 'today'.