Data Types

Understanding Data Types in Models

Overview

Models uses seven data types to perform calculations, described in the table below.

Data Type	Description	Link to Microsoft definition
Double	Double-precision floating point number (Excel default number type)	https://learn.microsoft.com/en-us/dotnet/api/system.double?view=net-8.0
Integer	Represents whole numbers from negative to positive 2,147,483,648 (32-bits)	https://learn.microsoft.com/en-us/dotnet/api/system.int32?view=net-8.0
String	Represents a text value	https://learn.microsoft.com/en-us/dotnet/api/system.string?view=net-8.0
DateTime	Represents a point in time	https://learn.microsoft.com/en-us/dotnet/api/system.datetime?view=net-8.0
Date	Represents a specific day	https://learn.microsoft.com/en-us/dotnet/api/system.dateonly?view=net-8.0
Boolean	Logical value (TRUE or FALSE)	https://learn.microsoft.com/en-us/dotnet/api/system.boolean?view=net-8.0
Validation	Represents a value from a list of choices - each value is the Child Tag of a Dimension

The default Data Type for a Variable is Double.

Conversions

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 Type Double, the calculation will be performed with Double types, and then converted to an Integer (whole number).

You should bear in mind the following notes on conversions when using multiple Data Types.

Data Type	Conversion notes
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

DateTime/Date treatments

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.

Comparison 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.