# 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:

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

If you’ve seen the formula where the Min, Max, Sum, or Count function is used with a single argument, it’s most likely that the user is trying to calculate the corresponding value for all records in the table.

By design, all these 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 all records in the table, follow these steps:

1. Create a many-to-many relation with itself.
2. Use the newly created relation to create a summary column with the corresponding aggregate function to calculate the value.

After that, you’ll be able to use the newly created summary column in the 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 and `ToDate([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 and `ToDate([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 and `ToDate([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 and `ToDate([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 and `FirstDayOfWeek(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 and `FirstDayOfWeek(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 and `FirstDayOfWeek(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 and `FirstDayOfMonth(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 and `FirstDayOfMonth(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 and `FirstDayOfMonth(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 and `FirstDayOfQuarter(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 and `FirstDayOfQuarter(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 and `FirstDayOfQuarter(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 and `FirstDayOfYear(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 and `FirstDayOfYear(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 and `FirstDayOfYear(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#, and `Time(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.