Data Types
Understanding Data Types in Models
Last updated
Understanding Data Types in Models
Last updated
Models uses seven data types to perform calculations, described in the table below.
Data Type | Description | Link to Microsoft definition |
---|---|---|
The default Data Type for a Variable is Double
.
Models calculates values based on the Data Type of each Variable - if you use different Data Types in your Model, values will be converted to the Data Type of their Variable.
For instance, if you have a Variable with Data Type
Integer
, which performs a Calculation based on two Variables with Data TypeDouble
, the calculation will be performed withDouble
types, and then converted to anInteger
(whole number).
You should bear in mind the following notes on conversions when using multiple Data Types.
Data Type | Conversion notes |
---|---|
Both DateTime
and Date
are treated in the same way as Excel treats Dates (see here), with 1st January 1900 stored as 1, with each subsequent day increasing the value by 1. Before performing calculations, Models converts Dates to serial numbers to ensure that calculations are performed identically to Excel.
Models treats a Date
as midnight at the end of the day when converting to DateTime
.
Note, that Excel has a bug (described here), in which 1900 is incorrectly assumed to be a leap year. Models replicates this bug to ensure consistency with Excel.
Excel uses a smaller set of Data Types than Models:
Number
Text
Logical (TRUE
or FALSE
)
Error
Excel does not differentiate between Double
and Integer
types, so this should be taken into account when generating Excel. You will need to use a rounding function in your Model if you want to force Excel to output an Integer
.
Dates in Excel are stored as numbers, as described above.
Excel does not automatically convert Logical values to 1 (TRUE
) and 0 (FALSE
).
For instance, if cells A1 and B1 have values TRUE and FALSE respectively, then A1 + B1 = 1, but SUM(A1, B1) = 0.
Using an equivalent SUM formula in Models would produce the result 1; therefore, it is important, if generating Excel from Models, to check that such calculations produce the desired result.
As a rule of thumb, applying an operator to a
TRUE
/FALSE
value (e.g. +, *) ensures that the value is converted to a number.
Double
Double-precision floating point number (Excel default number type)
Integer
Represents whole numbers from negative to positive 2,147,483,648 (32-bits)
String
Represents a text value
DateTime
Represents a point in time
Date
Represents a specific day
Boolean
Logical value (TRUE or FALSE)
Validation
String
Text values cannot be converted to numerical values. An error will be shown.
DateTime
and Date
See details below
Boolean
TRUE
is converted to 1, and FALSE
converted to 0 when performing calculations (e.g., 100 * TRUE = 100)
Validation
A Validation
value is treated as the text value of the name of its associated Tag