Tips and Tricks

Duration in Years

There is no way to represent a duration in years due to a different number of days in a year. You should adjust the formula expression to use the AdjustYear function.

Duration in Months

There is no way to represent a duration in months due to a different number of days in a month. You should adjust the formula expression to use the AdjustMonth function.

Duration in Days

To represent duration in days, you can use a duration literal - a number followed by a single character representing the unit of measurement. Use d for days. For example, 5d represents 5 days. Additionally, you can use the Days function to construct a duration in days. For instance, Days([Numeric Column]) converts the numeric value from "Numeric Column" into a duration in days.

To adjust the date or timestamp value, you can simply use the arithmetical operator + or operator - between the date/timestamp and the duration.

For example:

New Line for Text

To insert a new line into text, please use the following string "\n". For example, all of the following formulas:

will produce the same result:

aaa
bbb
ccc

Extracting Substring without Find function

There is no Find function that finds the position of one string in another, so you should use the following functions instead:

Extracting Column Value from Previous Record

To extract a column value from the previous record, follow these steps:

  1. Create a many-to-many relation to itself with a matching condition to filter previous records.
  2. Use the newly created relation to create a summary column with the "Index" aggregate function and corresponding sorting to extract the value from the most recent record.

Extracting Column Value from a Related Record

If you have a reference column in a table and wish to extract an additional column value from the related table, you need to create a lookup column using the corresponding relation. After that, you’ll be able to use the newly created column in the formula.

Summarize Column Values over Related Records

If you wish to summarize a column value from the related table, you need to create a summary column using the corresponding relation. After that, you’ll be able to use the newly created column in the formula.

Formula URL Parameter Encoding

Ensure that all calculated parameters for the Formula - URL column are encoded using the URLEncode function.

For example:

"https://www.google.com/search?q=" & URLEncode([Text Column])

Min, Max, Sum, or Count Functions and Several Records

The Min, Max, Sum, or Count functions expect at least two arguments and calculate their value using these arguments within a single record.

To calculate the Min, Max, Sum, or Count value over several records in the table, follow these steps:

  1. Create a many-to-many relation with itself.
  2. Add a match condition if you need to filter related records using column values from the current record.
  3. Use the newly created relation to create a summary column with the corresponding aggregate function to calculate the value and an optional filter condition to filter related records.

After that, you’ll be able to use the newly created summary column in your formula.

How to Create a Unique Random Identifier with Numbers and Letters

Please consider using the Guid function for that. It returns a unique string with 32 characters.

For example:

Your provided text is already quite well-written, but there are a few minor grammar and spelling adjustments to make it even clearer:

Filter Records by Date and Timestamp Columns

To check if a date or timestamp falls within a specific date range, please use the following or similar formulas:

You can vary the comparison operator and use the Between function and logical operators to create complex conditions.

How to Construct a Time Value

To construct a time value, you have the following options:

How to Construct a Date Value

To construct a date value, you have the following options:

Unix Epoch Time Conversions

Unix Epoch Time is the number of milliseconds since 1970-01-01 00:00 UTC.

The system does not support millisecond time precision, so the value should be converted to the number of seconds by dividing the number by 1000, then adding it to the Unix Epoch start time.

#1970-01-01 00:00# + Seconds([Unix Epoch Time] / 1000)

Conversion to Unix Epoch time is the reverse operation:

ToSeconds([Timestamp Column] - #1970-01-01 00:00#) * 1000

How to Calculate Number of Days between Dates

To calculate the number of days between dates, you need to subtract one date from another. The result will be a duration value. To convert the duration to the number of days, you should use the ToDays function.

For example:

How to Calculate Duration between Timestamps

To calculate the duration between timestamps, you need to subtract one timestamp from another. The result will be a duration value. To convert the duration to a number of days, hours, minutes, or seconds, you should use the ToDays, ToHours, ToMinutes, or ToSeconds function, respectively.

For example: