Meaning of two minus signs together (“double unary

2020-02-01 23:33发布

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.

1条回答
甜甜的少女心
2楼-- · 2020-02-02 00:37

@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'.

查看更多
登录 后发表回答