Part of Understanding the Calc Editor. Date/Time expressions return a specific point in time. Duration expressions return a length of time, such as three days or two hours.
The two are related without being interchangeable. A Date/Time value can be shifted by a Duration, but a Date/Time cannot be added to another Date/Time.
Date arithmetic
Sum-Datetime-And-Duration and Diff-Datetime-And-Duration both take a datetime first and a duration second, and each returns a datetime shifted forward or back by that duration. These are the only two expressions that mix the types.
The two types mix in one direction only
A Duration shifts a Date/Time, and Sum-Datetime-And-Duration and Diff-Datetime-And-Duration are the only expressions that combine the two. Duration's own Difference works on duration terms, so it is not the way to measure the gap between two Date/Time values.
Date/Time: values and fields
Expression | What it does |
|---|---|
Constant | A fixed value of this type. |
Field | A reference to a field of this type. |
If | Evaluates a boolean to decide which result to use. |
If-Set | Uses one value if a field has a value, and another if it does not. |
Integration-Data | A value of this type sourced from integration data. |
Unset | An explicitly unset date/time. |
Date/Time: arithmetic, comparison and branching
Expression | What it does |
|---|---|
Sum-Datetime-And-Duration | Adds a duration to a datetime, returning a datetime. |
Diff-Datetime-And-Duration | Subtracts a duration from a datetime, returning a datetime. |
Today | The current date. |
Now | The current date and time. |
Parse-Formatted-String | Parses a date/time out of a formatted string. |
Max, Min | The later or earlier of two date/time values. |
Max-Array, Min-Array | The latest or earliest date/time across an array column. |
Switch-On-Integer, Switch-On-String | Selects one of a group of date/time values by matching an integer or a string. |
Duration: values and fields
Expression | What it does |
|---|---|
Constant | A fixed value of this type. |
Field | A reference to a field of this type. |
If | Evaluates a boolean to decide which result to use. |
If-Set | Uses one value if a field has a value, and another if it does not. |
Integration-Data | A value of this type sourced from integration data. |
Unset | An explicitly unset duration. |
Duration: arithmetic and parsing
Expression | What it does |
|---|---|
Difference | The difference between two duration terms. |
Sum | The total of a fixed set of duration values. |
Now (Time Of Day) | The current time of day, as a duration since midnight. |
Parse-Formatted-String | Parses a duration out of a formatted string. |
Switch-On-Integer, Switch-On-String | Selects one of a group of duration values by matching an integer or a string. |
Duration: across a set or an array
Expression | What it does |
|---|---|
Average-Durations | The mean of a fixed set of duration values. |
Average-Array-Durations | The mean duration across an array column. |
Max, Min | The longer or shorter of two duration values. |
Max-Array, Min-Array | The longest or shortest duration across an array column. |
Sum-Array-Durations | The total duration across an array column. |