General Caster

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.

TEXT(WORKDAY(DATEVALUE({item's Date}), {item's Number}), "YYYY-MM-DD")

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.

CONCATENATE(TEXT(DATEVALUE({item's Date}) + 7, "YYYY-MM-DD"), "+", TEXT(DATEVALUE({item's Date}) + 21, "YYYY-MM-DD"))

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.

CONCATENATE(YEAR(TODAY()) + IF(TODAY() > DATEVALUE(CONCATENATE(YEAR(TODAY()), RIGHT({item's Birth Date}, 6))), 1, 0), RIGHT({item's Birth Date}, 6))

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.

CONCATENATE(TEXT(DATEVALUE(LEFT({item's Timeline}, 10)) + 14, "YYYY-MM-DD"), "+", TEXT(DATEVALUE(RIGHT({item's Timeline}, 10)) + 14, "YYYY-MM-DD"))

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.

IF({item's Number} > 100000, 5, IF({item's Number} > 75000, 4, IF({item's Number} > 50000, 3, IF({item's Number} > 25000, 2, 1))))

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.

SWITCH({item's Status}, "Tim", "tim@apple.com:Tim Apple", "Jeff", "jeff@amazon.com:Jeff Amazon", "")

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.

RIGHT({item's Location}, LEN({item's Location}) - FIND(":", {item's Location}, FIND(":", {item's Location}) + 1))

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.

(LEN({item's Subitems Text}) - LEN(SUBSTITUTE({item's Subitems Text}, "Apple", ""))) / LEN("Apple")

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.

CONCATENATE("http://wa.me/", LEFT({item's Phone}, FIND(":", {item's Phone}) - 1), ":WhatsApp")

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.

NETWORKDAYS(DATEVALUE(LEFT({item's Timeline}, 10)), DATEVALUE(RIGHT({item's Timeline}, 10)))

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.

((NETWORKDAYS(DATEVALUE({item's From DateTime}), DATEVALUE({item's To DateTime})) - 1) * (TIMEVALUE("18:00") - TIMEVALUE("09:00")) + IF(NETWORKDAYS({item's To DateTime}, {item's To DateTime}), MEDIAN(MOD(TIMEVALUE({item's To DateTime}), 1), TIMEVALUE("18:00"), TIMEVALUE("09:00")), TIMEVALUE("18:00")) - MEDIAN(NETWORKDAYS(DATEVALUE({item's From DateTime}), DATEVALUE({item's From DateTime})) * MOD(TIMEVALUE({item's From DateTime}), 1), TIMEVALUE("18:00"), TIMEVALUE("09:00"))) * 24

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.

TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 7), 3), "YYYY-MM-DD")

Ideas