# Ideas

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...