Comparisons and Data Types (Formulas)

When you use columns of different data-types, the inequality operators have a specific set of behaviours based on that exhibited by Excel and other spreadsheet software. If the operands for a comparison function are of different data types, the values will not be compared.

Each type is given an ordinal value:

Type Ordinal
Number 1
DateTime 2
String 3
Boolean 4

The values for the types are then compared.
Thus:

  • A boolean is always greater than a string, datetime, or number
  • A number is always less than a datetime, string, or boolean

Our suggestion: Do not compare values of different types. Instead only compare values of the same type, and convert values if you need to.

Related Articles