Formulas and use cases to solve common problems and get inspiration from

Formulas in this page include standard column references (i.e.

`{item's Date}`

).Replace them with the text that appears when you click on the corresponding column button at the bottom of the formula composer.

From date to date

**Scenario:** when you set a date, set a due date after the number of working days set in another column

**Requirements:** two date columns

**Integration:** When any column changes, perform **formula** and cast result to **column**

**Functions:**

**TEXT:**trasforms a date into`YYYY-MM-DD`

format;**WORKDAY:**adds working days to a date;**DATEVALUE:**converts a column value into a valid date, you can then add or subtract days with a simple math operation.

From date to timeline

**Scenario:** when you set a date, set a timeline starting 7 days after and ending 21 days after

**Requirements:** one date column and one timeline column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**CONCATENATE:**creates the`from_date+to_date`

string accepted by Timeline column;**TEXT:**trasforms a date into`YYYY-MM-DD`

format;**DATEVALUE:**converts a column value into a valid date, you can then add or subtract days with a simple math operation.

From birth date to next birthday

**Scenario:** when you set a birth date, set the next birthday

**Requirements:** two date columns

**Integration:** When any column changes, perform **formula** and cast result to **column**

**Functions:**

**CONCATENATE:**builds the date attaching the current or next year to the monday and the day;**YEAR:**gets the year from a date;**TODAY:**gets the current date;**IF:**if first statement is true, returns second value, third otherwise;**DATEVALUE:**converts a column value into a valid date, you can then add or subtract days with a simple math operation;**RIGHT:**gets*n*chars of text starting from end.

From timeline to timeline

**Scenario:** when you set a timeline, set another timeline shifted 14 days in the future

**Requirements:** two timeline columns

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**CONCATENATE:**creates`from_date+to_date`

string accepted by Timeline column;**TEXT:**trasforms a date into`YYYY-MM-DD`

format;**DATEVALUE:**converts a column value into a valid date, you can then add or subtract days with a simple math operation;**LEFT:**picks`from_date`

from the`from_date+to_date`

string;**RIGHT:**picks`to_date`

from the`from_date+to_date`

string.

From number to rating

3

**Scenario:** add a rating based on budget amount

**Requirements:** one number column and one rating column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**IF:**if first statement is true, returns second value, third otherwise.

From status to email

**Scenario:** set a clickable email address based on item's status

**Requirements:** one status column with "Tim" and "Jeff" values and one email column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**SWITCH:**if first expression is equal to second value, returns thirds. If it's equal to fourths, returns fifths. Sixths otherwise.

From location to text

**Scenario:** retrieve address as text from location

**Requirements:** one location column and one text column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**RIGHT:**gets*n*chars of text starting from end;**LEN:**gets length of text;**FIND:**finds position of first occurrence of char in text.

Occurrences in subitems

3

**Scenario:** count occurrences of "Apple" in any subitems column type

**Requirements:** one subitems summary column (to hide) and one number column

**Integration:** When any column changes, perform **formula** and cast result to **column**

**Functions:**

**LEN:**gets length of text;**SUBSTITUTE:**replaces text with other text.

From phone to WhatsApp

**Scenario:** create a link to send a WhatsApp message

**Requirements:** one phone column and one link column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**LEFT:**gets*n*chars of text starting from beginning;**FIND:**finds position of first occurrence of char in text;**CONCATENATE:**creates the`URL:title`

string to be casted into link column.

From timeline to working days

**Scenario:** get working dates between two dates

**Requirements:** one timeline column and one number column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**NETWORKDAYS:**calculates working days between two dates;**DATEVALUE:**converts a column value into a valid date, you can then add or subtract days with a simple math operation;**LEFT:**picks`from_date`

from the`from_date+to_date`

string;**RIGHT:**picks`to_date`

from the`from_date+to_date`

string.

From dates to working hours

**Scenario:** get working hours between two dates with times in a 09:00-18:00 (9AM-6PM) work schedule

**Requirements:** two date columns with time and one number column

**Integration:** When **column** changes, perform **formula** and cast result to **column**

**Functions:**

**NETWORKDAYS:**calculates working days between two dates;**DATEVALUE:**converts a column value into a valid date, you can then add or subtract days with a simple math operation;**TIMEVALUE:**converts a column value into a valid time in a base-24 format;**IF:**if first statement is true, returns second value, third otherwise;**MEDIAN:**gets the median in a set of numbers;**MOD:**gets the remainder of a division.

First Monday of the month

**Scenario:** set first Monday of the month as due date for a new task

**Requirements:** one date column

**Integration:** When an item is created, perform **formula** and cast result to **column**

**Functions:**

**TEXT:**trasforms a date into`YYYY-MM-DD`

format;**DATE:**creates a date from year, month and day;**YEAR:**gets the year from a date;**MONTH:**gets the month from a date;**TODAY:**gets the current date;**WEEKDAY:**gets the week day from a date.

More than **20,352,000** operations performed by over **2,700** users. And counting...