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:
[Date Column] + 10d
adds 10 days to the date stored in the "Date Column".#2023-01-20# - Days(5)
subtracts 5 days from January 20, 2023, and returns January 15, 2023.
New Line for Text
To insert a new line into text, please use the following string "\n"
. For example, all of the following formulas:
"aaa\nbbb\nccc"
List("\n", "aaa", "bbb", "ccc")
"aaa" & "\n" & "bbb" & "\n" & "ccc"
Concat("aaa", "\n", "bbb", "\n", "ccc")
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:
- To extract a substring before the first occurrence of a delimiter string, use the Left function. For example,
Left("aaa.vs.bbb.vs.ccc", ".vs.")
returns "aaa". - To extract a substring after the first occurrence of a delimiter string, use the NotLeft function. For example,
NotLeft("aaa.vs.bbb.vs.ccc", ".vs.")
returns "bbb.vs.ccc". - To extract a substring after the last occurrence of a delimiter string, use the Right function. For example,
Right("aaa.vs.bbb.vs.ccc", ".vs.")
returns "ccc". - To extract a substring before the last occurrence of a delimiter string, use the NotRight function. For example,
NotRight("aaa.vs.bbb.vs.ccc", ".vs.")
returns "aaa.vs.bbb". - To extract a substring from text if you know the particular position and length, use the Mid function. For example,
Mid("type20A", 5, 2)
returns the text value "20". - To extract a substring from text with parts separated by a delimiter, use the Part function. For example,
Part("aaa|bbb|ccc", 2, "|")
returns the text value "bbb".
Extracting Column Value from Previous Record
To extract a column value from the previous record, follow these steps:
- Create a many-to-many relation to itself with a matching condition to filter previous records.
- 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:
- Create a many-to-many relation with itself.
- Add a match condition if you need to filter related records using column values from the current record.
- 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:
Guid()
returns "3A376A6C-46BA-4D56-BAFF-BFD3A3EDCE2F".Replace(Guid(), "-", "")
returns an identifier without a dash: "3A376A6C46BA4D56BAFFBFD3A3EDCE2F".Right(Guid(), 10)
returns a 10-character unique identifier: "D3A3EDCE2F".
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:
- To check if the date is the current day, use the following formulas:
[Date Column] = Today()
for the date column andToDate([Timestamp Column]) = Today()
for the timestamp column. - To check if the date is yesterday, use the following formulas:
[Date Column] = Today() - 1d
for the date column andToDate([Timestamp Column]) = Today() - 1d
for the timestamp column. - To check if the date is N days in the future, use the following formulas:
[Date Column] = Today() + 5d
for the date column andToDate([Timestamp Column]) = Today() + 5d
for the timestamp column to check if it’s 5 days in the future. - To check if the date is N days in the past, use the following formulas:
[Date Column] = Today() - 5d
for the date column andToDate([Timestamp Column]) = Today() - 5d
for the timestamp column to check if it’s 5 days in the past. - To check if the date is the current week, use the following formulas:
FirstDayOfWeek([Date Column]) = FirstDayOfWeek(Today())
for the date column andFirstDayOfWeek(ToDate([Timestamp Column])) = FirstDayOfWeek(Today())
for the timestamp column. - To check if the date is the last week, use the following formulas:
FirstDayOfWeek([Date Column]) = FirstDayOfWeek(Today() - 7d)
for the date column andFirstDayOfWeek(ToDate([Timestamp Column])) = FirstDayOfWeek(Today() - 7d)
for the timestamp column. - To check if the date is the next week, use the following formulas:
FirstDayOfWeek([Date Column]) = FirstDayOfWeek(Today() + 7d)
for the date column andFirstDayOfWeek(ToDate([Timestamp Column])) = FirstDayOfWeek(Today() + 7d)
for the timestamp column. - To check if the date is the current month, use the following formulas:
FirstDayOfMonth([Date Column]) = FirstDayOfMonth(Today())
for the date column andFirstDayOfMonth(ToDate([Timestamp Column])) = FirstDayOfMonth(Today())
for the timestamp column. - To check if the date is the last month, use the following formulas:
FirstDayOfMonth([Date Column]) = FirstDayOfMonth(AdjustMonth(Today(), -1))
for the date column andFirstDayOfMonth(ToDate([Timestamp Column])) = FirstDayOfMonth(AdjustMonth(Today(), -1))
for the timestamp column. - To check if the date is the next month, use the following formulas:
FirstDayOfMonth([Date Column]) = FirstDayOfMonth(AdjustMonth(Today(), 1))
for the date column andFirstDayOfMonth(ToDate([Timestamp Column])) = FirstDayOfMonth(AdjustMonth(Today(), 1))
for the timestamp column. - To check if the date is the current quarter, use the following formulas:
FirstDayOfQuarter([Date Column]) = FirstDayOfQuarter(Today())
for the date column andFirstDayOfQuarter(ToDate([Timestamp Column])) = FirstDayOfQuarter(Today())
for the timestamp column. - To check if the date is the last quarter, use the following formulas:
FirstDayOfQuarter([Date Column]) = FirstDayOfQuarter(AdjustMonth(Today(), -3))
for the date column andFirstDayOfQuarter(ToDate([Timestamp Column])) = FirstDayOfQuarter(AdjustMonth(Today(), -3))
for the timestamp column. - To check if the date is the next quarter, use the following formulas:
FirstDayOfQuarter([Date Column]) = FirstDayOfQuarter(AdjustMonth(Today(), 3))
for the date column andFirstDayOfQuarter(ToDate([Timestamp Column])) = FirstDayOfQuarter(AdjustMonth(Today(), 3))
for the timestamp column. - To check if the date is the current year, use the following formulas:
FirstDayOfYear([Date Column]) = FirstDayOfYear(Today())
for the date column andFirstDayOfYear(ToDate([Timestamp Column])) = FirstDayOfYear(Today())
for the timestamp column. - To check if the date is the last year, use the following formulas:
FirstDayOfYear([Date Column]) = FirstDayOfYear(AdjustYear(Today(), -1))
for the date column andFirstDayOfYear(ToDate([Timestamp Column])) = FirstDayOfYear(AdjustYear(Today(), -1))
for the timestamp column. - To check if the date is the next year, use the following formulas:
FirstDayOfYear([Date Column]) = FirstDayOfYear(AdjustYear(Today(), 1))
for the date column andFirstDayOfYear(ToDate([Timestamp Column])) = FirstDayOfYear(AdjustYear(Today(), 1))
for the timestamp column.
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:
- Use a literal to build a constant time value. Time values should be written using the 24-hour clock format with a colon as a separator, like
#23:30#
or#09:15:30#
. - Use the Time function to construct a value from numeric parts such as hours, minutes, and seconds. For example,
Time(22, 15)
returns the time value #22:15#,Time(22, 15, 5)
returns the time value #22:15:05#, andTime(246)
returns the time value #00:04:06#. - Use the ToTimeOfDay function to convert a text value into a time. The text can represent various popular time formats, including "3 pm", "3:04 pm", "22:00", "2:03:29 am", and "12:03:29.345". For example,
ToTimeOfDay("3:04 pm")
returns the time value #15:04#. - Use the ToTimeOfDay function to extract the time part of a timestamp. For example,
ToTimeOfDay([Timestamp Column])
returns the time value from the "Timestamp Column".
How to Construct a Date Value
To construct a date value, you have the following options:
- Use a literal to build a constant date value. Date values should be written in the year-month-day format using dashes as separators, such as
#2009-10-30#
. - Use the Date function to construct a value from numeric parts such as year, month, and day. For example,
Date(2012, 8, 20)
returns the date value #2012-08-20#. - Use the ToDate function to convert a text value into a date. The text can be in several popular date formats, including "January 15, 2012", "1/15/12", "2012/1/15", and "1-15-2012". For example,
ToDate("1/15/12")
returns the date value #2012-01-15#. - Use the ToDate function to extract the date part of a timestamp. For example,
ToDate([Timestamp Column])
returns the date value from the "Timestamp Column".
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:
[End Date] - [Start Date]
returns the the duration value in days between the dates in the "Start Date" and "End Date" columns.ToDays([End Date] - [Start Date])
returns the number of days between the dates in the "Start Date" and "End Date" columns.ToDays(Today() - [Date Column])
returns the number of days between today and the date in the "Date Column" column.
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:
[End Timestamp] - [Start Timestamp]
returns the duration between the timestamps in the "Start Timestamp" and "End Timestamp" columns.ToDays([End Timestamp] - [Start Timestamp])
returns the number of days between the timestamps in the "Start Timestamp" and "End Timestamp" columns.ToHours(Now() - [Timestamp Column])
returns the number of hours between the current timestamp and the timestamp in the "Timestamp Column" column.ToMinutes(Now() - [Timestamp Column])
returns the number of minutes between the current timestamp and the timestamp in the "Timestamp Column" column.ToSeconds(Now() - [Timestamp Column])
returns the number of seconds between the current timestamp and the timestamp in the "Timestamp Column" column.