7. Value types

Most operators expect their operands to have a specific type and return a result having a specific type. Examples: + expects 2 numbers and returns a number, & expect two strings and returns a string.

Most functions expect their arguments to have a specific type and return a result having a specific type. Examples: left() expects a string and a number and returns a string, cos() expects a number and returns a number.

When the operand or the argument does not have the expected type, an automatic conversion is performed if this is possible; otherwise an error is reported.

Example that works: cos(B2) where cell B2 contains a string "3.14" which can be parsed as a number.

Example that sort of works: 2 + "two" (gives ``number'' NaN, which is not really a number).

Example that reports an error (because function sum() has been specified to do that): sum(2, "two").

This automatic conversion process is detailed in next section.

The types of values returned by expressions are:


Examples: .23, 3.14, 314E-2, PI(), 2+2, log(A2,10).


Examples: "Great", 'Not that great', "black" & "white", mid(D3, 3, 4).


Examples: TRUE, FALSE, true(), false(), 1>=0, and(A1 >= 1, A1 <= 99).


A set of XML nodes: Examples: A1, A:A, $2:$3, "table-23"!$A$1, $(vat), `id("product-list")/*`, apply("x + 1", B:B).


Year/month/day. Examples: today(), date(1960,3,16), datevalue("1960-03-16"), datevalue("3/16/60", "MM/dd/yy").


Hour/minute/seconds/fraction of a second. Examples: time(13,30,45), timevalue("13:30:45"), timevalue("1:30:45 PM", "hh:mm:ss a").


Year/month/day and hour/minute/seconds/fraction of a second: Examples: now(), datetime(1960,3,16,13,30,45), datetimevalue("1960-03-16T13:30:45"), datetimevalue("1:30:45.250 PM 3/16/60", "hh:mm:ss.SSS a MM/dd/yy").