Built-In Functions
There are several functions provided for special handling of dates when creating conditional expressions.
| function | result |
|---|---|
| Today() | returns today’s date for comparisons (e.g. “$EFF_DATE < Today()“) |
| Date(d) | encodes a date string (yyyy-mm-dd) for comparisons (e.g. “$EFF_DATE < Date(p1)“) |
| DateEqual(d1, d2) | true if the two dates are the same |
| DateBefore(d1, d2) | true if “d1” is before “d2” |
| DateOnOrBefore(d1, d2) | true if “d1” is equal to, or before “d2” |
| Len() | length of array, map or string |
| all() | will return true if all elements satisfy the predicate |
| none() | will return true if all elements do NOT satisfy the predicate |
| any() | will return true if any element satisfies the predicate |
| one() | return true if exactly ONE element satisfies the predicate |
| count() | returns number of elements what satisfies the predicate |
| trim(str[, chars]) | removes leading and trailing whitespace or specified characters from a string |
| trimPrefix(str, prefix) | removes the specified prefix from a string |
| trimSuffix(str, suffix) | removes the specified suffix from a string |
| upper(str) | converts a string to uppercase |
| lower(str) | converts a string to lowercase |
| indexOf(str, substring) | returns the index of the first occurrence of a substring in a string, or -1 if not found |
| hasPrefix(str, prefix) | returns true if the string starts with the specified prefix |
| hasSuffix(str, suffix) | returns true if the string ends with the specified suffix |
Date Functions
Two of the more useful date functions are:
Today() allows you to compare a date against today.
Filter: "$EFF_DATE != nil && $EFF_DATE < Today()"Date() converts a string date to the internal Excel date format.
Filter: "$EFF_DATE != nil && $EFF_DATE < Date('2020-01-01')"Because date comparisons are so common, we included three additional functions to make that easier.
For example, the following will only include rows where $EFF_DATE is on or before ‘2020-01-01’ (or empty):
Filter: "DateOnOrBefore($EFF_DATE, '2020-01-01')"ℹ️You do not need to test for “nil” (like normal) with these date functions because that check is performed internally.
ℹ️If $EFF_DATE in this case is nil (not defined), the function will return true and so that row would
be included in the filter.
Revised: 2020-12-05
Copied!