# Formula guide

## The Formula

The formula is used to perform a calculation and return a value using the values of one or more columns of a same record.

For example: given a table with two columns "Product Name" and "Cost", we can add a third formula column that will return the value of Cost column includes VAT.

## How to use the column values in the formula

You can use the value of the columns in the formula by writing the name of the column enclosed in square brackets.

Example:
[Name] + [Lastname]

If one of the columns used in the formula is not filled, formula does not return a result. To obtain nevertheless a result it is possible to make use of the function ISNULL() to return a default value chosen according to the needs. In the example below the ISNULL() function returns an empty text if the column is not filled: ISNULL([Name], '') + ISNULL([Lastname], '')

## How to write text values, numbers, date and yes/no into the formula

### Text

Text values must be written in quotes. If these marks are used in the text must be preceded by a quote.

Example:
[Name] + ' McGiver'

### Numbers

Numeric values must use the "." to separate decimals.

Example:
[Cost] + 5.25

### Date

The date values must be enclosed in "#" character, expressed in UTC and in the following format: month/day/year.

Example:
[Birthdate] < #1/31/1982#

### Yes/No

Yes/No values must be written true or false.

Example:
[IsValid] = true

## Using null values

Null values in a formula can be identified with NULL word.

Example:
[Name] = NULL

## Using operators in the formula

In a formula you can use the following operators:

+ (Addition)

- (Subtraction)

* (Multiplication)

/ (Division)

% (Module)

< (Less than)

> (Greater than)

<= (Less than or equal to)

>= (Greater than or equal)

<> (Not equals)

= (Equals)

IN (On the list)

LIKE (Contains)

AND

OR

### How to use the "IN" operator

The "IN" operator checks if a text value, numeric or date is in a list.

Examples:

## Wildcards in the text

The "*" and "%" can be used as wildcards in comparisons using the LIKE operator. If the text already contains these characters, then they must be enclosed in square brackets (eg [*] or [%]). If the brackets are already present in the text, then they must be enclosed in square brackets (eg [[] or []]). The wildcards are allowed only at the beginning and end of text and are not allowed in the middle of text.

Examples:

## Functions

In the formula you can use the following functions:

### LEN

Returns the number of characters in a text.

Example:
LEN('test')

### IIF

Gets one of two values depending on the result of a logical expression.

Example:
IIF([Cost] > 50, true, false)

### ISNULL

Returns a determined value if a given expression is null.

Example:
ISNULL([Cost], 20)

### TRIM

Remove the following characters in a text: \r \n \t, ' '.

Example:
TRIM('test ')

### SUBSTRING

Returns a text of specified length starting from a specific point.

Example:
SUBSTRING('sample text', 1, 5)

### DAY

Returns the day of the month of the given Date.

Example:
DAY([date])

### MONTH

Returns the month number of the Date.

Example:
MONTH([data])

### YEAR

Returns the year of the Date.

Example:
YEAR([data])

### DATEDIF

Returns the difference in number of days between two dates.

Example:
DATEDIF([date1],[date2])

### TIMEDIF

Returns the difference in minutes between two date-time. To return the hours divide the result by 60.

Example:
TIMEDIF([date1],[date2])

TIMEDIF([date1],[date2]) / 60

### ADDDAYS

Returns a new Date increased by a specified number of days. You can also subtract a number of days by entering a negative value.

Example:
ADDDAYS([date],3)

### ADDMONTHS

Returns a new Date increased by a specified number of months. You can also subtract a number of months by entering a negative value.

Example:
ADDMONTHS([date],3)

### ADDYEARS

Returns a new Date increased by a specified number of years. You can also subtract a number of years by entering a negative value.

Example:
ADDYEARS([date],3)