## What is the Formula Field Type?

Formula Field type allows you to perform various operations like arithmetic, conditionals, count, average, and many others on your board. Formula field allows you to make calculations like on excel software but in a better way.

With formula field type you can pull data from other fields and make calculations to build any information you need.

The formula type can perform string functions, Arithmetic functions, and date-processing functions so it acts on money fields, number fields, short text, dropdown, Radio buttons, date, time, and formula fields.

However, you cannot use formula field type to draw data from field types like Files, Link, Locations.

Formula field type only links to values in the cell inputs and not description or any other key details.

## How to create a Formula Field

To create the formula field:

Click on the + symbol to the right of the last field header in the Table view.

This pops up a form page, on which you click on Field type to select the formula field.

π‘ Note: The formula field type is a system-generated field type

Type in the Field Name and Description.

Click on Next

Type in the formula(click on a function to pick the functions you wish to perform and input field where you compute any logical calculation. ).

π‘ Note: The Syntax and Example give you an idea of what each function should look like when typing in the Formula.

Mark, any of the corresponding Checkboxes to show a better representation of the field on the board.

Click on Create Field

**FORMULA OPERATORS **

**1.String Functions**

**JOIN**

This operator joins or concatenates text together, it also operates on field types with strings

**EXAMPLE**

JOIN({'welcome'},{'to'}, {'Quabbly'})

**EXPECTED RESULT**

welcome to Quabbly

**LEFT**

Extracts a specified number of characters from a string from the left-hand side

**EXAMPLE**

LEFT({'Welcome'},4)

**EXPECTED RESULT**

Welc

**LEN**

Gives you the number of characters for given string characters in counts or numbers, for example, the number of characters in the word 'David' == 5

**EXAMPLE**

LEN({'Quabbly'})

**EXPECTED RESULT**

7

**LOWER**

Changes any record you pass in string characters to all lower cases

**EXAMPLE**

LOWER({'QUABBLY'})

**EXPECTED RESULT**

quabbly

**UPPER**

Changes any record you pass in string characters to all upper case

**EXAMPLE**

UPPER({'QUABBLY'})

**EXPECTED RESULT**

QUABBLY

**REPLACE**

Helps replace a part of a strIng character you pass as a record to a field with another character.

**EXAMPLE**

REPLACE({'Monday'},{0},{2},{'Fri'})

**EXPECTED RESULT**

Friday

**REPEATS**

The repeat function allows you present string characters a specified number of times.

**EXAMPLE**

REPEAT({'quabbly'},{2})

**EXPECTED RESULT**

quabblyquabbly

**RIGHT**

Extracts a specified number of string characters from a string all on the right-hand side

**EXAMPLE**

RIGHT({'Welcome'},{4})

**EXPECTED RESULT**

come

**SEARCH**

Search for a particular character within another string character, returns the value 'not' if it doesn't exist, and returns 'exist' if the character is present within the other

**EXAMPLE**

SEARCH({'Board'},{'Board Group'})

OR

SEARCH({'Board'},{'Teacup'})

**EXPECTED RESULT**

exist (because the word "board" exists in the word "board group")

OR

not (because the word "board" does not exist in the word "Teacup")

**SUBSTITUTE**

The substitute function helps you replace part of a string character by specifying the string character and the part you want to replace

**EXAMPLE**

SUBSTITUTE ({'Goodmorning'}, {'morning'}, {'night'})

**EXPECTED RESULT**

Goodnight

**TEXT**

This allows you to convert a number field type that doesn't support characters in a form that takes string characters

**EXAMPLE**

TEXT({200}, {$#,###.00})

**EXPECTED RESULT**

$200

**TRIM**

Removes whitespace from a text.

**EXAMPLE**

TRIM({' Jamiuozigi'})

**EXPECTED RESULT**

Jamiuozigi

**2. MATH FUNCTIONS**

**ABS**

Finds the absolute value of a number. An absolute number is the number of units that separates it from a zero, it's never a negative value. For instance the absolute number for 5 abs(5)=5 which is the same as abs (-5)=5

**EXAMPLE**

ABS(-5) or ABS(5)

**EXPECTED RESULT**

5

**LOG**

Returns the logarithmic value of a numeric no

**EXAMPLE**

LOG(5)

**EXPECTED RESULT**

0.6989

**SQRT**

Gives you the number of characters for given string characters in counts or numbers, for example, the number of characters in the word 'David' == 5

**EXAMPLE**

Returns the square root of a numeric field type

**EXPECTED RESULT**

4

**CBRT**

Returns the cubic root for a value in the numeric field type

**EXAMPLE**

CRBT(27)

**EXPECTED RESULT**

3

**ROUND_UP**

This function rounds up a number to the highest value after the decimal.

**EXAMPLE**

ROUND_UP(7.57)

**EXPECTED RESULT**

8

**ROUND_DOWN**

Rounds down a number to the lowest value after the decimal.

**EXAMPLE**

ROUND_DOWN(7.57)

**EXPECTED RESULT**

7

**SIN**

Returns the sine of a value in a numeric field type

**EXAMPLE**

SIN(2.45)

**EXPECTED RESULT**

0.63776

**COS**

Returns the cosine of a value in a numeric field type

**EXAMPLE**

COS (2.45)

**EXPECTED RESULT**

-0.77023

**TAN**

Returns the tangent of a value in a numeric field type

**EXAMPLE**

TAN (2.45)

**EXPECTED RESULT**

-0.82802

**AVG**

Finds the average of numbers

**EXAMPLE**

AVG(3, 5, 7)

**EXPECTED RESULT**

5

**DIV**

The division operator gives a quotient value when a field A (Dividend) is divided by another field B( divisor).

**EXAMPLE**

DIV(20, 10)

**EXPECTED RESULT**

2

**SUM**

This is known as the addition operator. You can use the addition operator to add numbers from different fields. With an additional operator, you can concatenate strings from different fields.

**EXAMPLE**

SUM(20,10)

**EXPECTED RESULT**

30

**MUL**

The multiplication operator allows a number in a field to have a multiplying factor on another field.

**EXAMPLE**

MUL(20,10)

**EXPECTED RESULT**

200

**MOD**

The modulus is **another name for the remainder after division**.

**EXAMPLE**

MOD(17,5)

**EXPECTED RESULT**

2, since if we divide 17 by 5, we get 3 with the remainder 2.

**SUB**

This is known as the subtraction operator and it works on numbers and money fields. With this operator, you can derive the difference between numbers in the fields.

**EXAMPLE**

SUB(15,5)

**EXPECTED RESULT**

3

**MIN**

This operator works on numbers and money fields. With this operator, you can derive the** smallest value** in the fields.

**EXAMPLE**

MIN(15,5,10,20,2,17)

**EXPECTED RESULT**

2

**MAX**

This operator works on numbers and money fields. With this operator, you can derive the** highest value** in the fields.

**EXAMPLE**

MAX(15,5,10,20,2,17)

**EXPECTED RESULT**

20

# 3. **DATE PROCESSING FUNCTIONS**

**DAY ({Created Date})**

This formula extracts the day from a specified date

**SYNTAX**

DAY({Date}) DAY({DATE AND TIME})

**WEEK**

The date function allows you to extract the week number in a year based on a specific date

**SYNTAX**

WEEK ({DATE}), WEEK ({DATE AND TIME})

**MONTH**

This date function returns the month from a specified date

**SYNTAX**

MONTH ({DATE}), MONTH({DATE AND TIME})

**HOUR**

This date function operates on the time and date-time field to return the hour of the day as a number from 0 to 23- hour

**SYNTAX**

HOUR({DATE AND TIME}), HOUR({TIME})

**MINUTE**

This date function operates on the time and date-time field to return a minute in a time as a number from 0 to 59

**SYNTAX**

MINUTE({DATE AND TIME}), MONTH({TIME})

**SEC**

This date function operates on the time and date-time field to return seconds in a time as a number from 0 to 59

**SYNTAX**

SEC({DATE AND TIME}), SEC({CREATED TIME}), SEC ({TIME})

**TODAY - should evaluate to the current date at midnight**

This function returns the current date from midnight

**SYNTAX**

TODAY()

**NOW - should evaluate to current date and time**

This function helps evaluate the current date and time, current date from midnight, and time-based on a 24-hour clock

**SYNTAX**

NOW()

**YESTERDAY - should evaluate to yesterday at midnight**

This function returns yesterday's date from the current date from midnight

**SYNTAX**

YESTERDAY()

**NOW_YESTERDAY - should evaluate to this time yesterday**

This function evaluates from the current time to yesterday's date.

**SYNTAX**

DAYS_BETWEEN(TODAY(),{Last Visit}),

**DAYS_BETWEEN(x, y) e.g. DAYS_BETWEEN(TODAY(), {Last Visit})**

Takes two date parameters and returns the difference between two dates

**SYNTAX**

NOW_YESTERDAY()

**ADD_DAY({Date},y) .. returns a new date**

This function takes one date parameter and number, it adds a number to the specified date to return a new value

**SYNTAX**

ADD_DAYS()

**SUBTRACT_DAY({Date}, y) .. returns a new date**

This function takes one date parameter and number, it removes a number from the specified date to return a new value

**SYNTAX**

ADD_DAYS

SUBSTRACT_DAYS(x)