## Formula column for SeaTable

One of the most powerful features of SeaTable is the formula column. With formulas you can automatically transform, calculate, combine or merge the values of other columns from the current table or even from another (linked) table of the same base. On top of that, you can of course add other formula columns that take the results from the existing formal columns and calculate them. The formula columns that depend on the other columns are automatically updated as soon as the values there are changed.

Currently, SeaTable offers the following features:

- Constants
- Operands
- Mathematical functions
- Text functions
- Date functions
- Logical functions
- Statistical functions
- Link functions

Außer den oberen Funktionen gibt es im Formeleditor noch 1 bzw. mehrern Spaltenlisten. Die sind die Spalten in der aktuellen Tabelle und die verlinkten Spalten der anderen Tabelle in der Base. Wenn Sie auf das “+” Symbol daneben klicken, wird der Spaltenname als Element in den Eingabefeld hinzugefügt, und zwar in dem Format {Spaltename} bzw. {VerlinktenTabelle.Spaltenname}. Dieses Element können Sie direkt in Ihrer Formel nutzen. Alternativ können Sie natürlich einen Spaltenname in der Form von {Spaltenname} per Hand eingeben.

In this article, we will show you a complete overview of all formulas in SeaTable with examples. If you are looking for a specific function, you can use the

## SeaTable's formulary with examples

### Constants

Operator | Description | Input | Result |
---|---|---|---|

e | Returns the Euler number e=2.71828... | e+1 | 3.71828183 |

pi | Returns the circle number Pi. | pi | 3.14159265 |

true() | Returns the logical value 'true'. | true() | true |

false() | Returns the logical value 'false'. | false() | false |

### Operands

Operator | Description | Input | Result |
---|---|---|---|

add(num1, num2) | Adds two numeric values (num1 and num2) and returns the result. | add(1, 2) | 3 |

Alternative: + | 1+2 | 3 | |

subtract(num1, num2) | Subtracts one numeric value (num2) from another (num1). | subtract(5, 4) | 1 |

Alternatively: - | 5-4 | 1 | |

multiply(num1, num2) | Multiplies two numeric values. | multiply(3, 4) | 12 |

Alternatively: * | 3*4 | 12 | |

divide(num1, num2) | Divides one numeric value (num1) by another (num2). | divide(3, 2) | 1.5 |

Alternative: / | 3/2 | 1.5 | |

mod(num1, num2) | Calculates the remainder of a division. | mod(15, 7) | 1 |

Alternative: % | 15 % 7 | 1 | |

power(num1, num2) | Calculates the power (num2) of a number (num1). | power(3, 2) | 9 |

Alternatively: ^ | 3 ^ 2 | 9 | |

greater(num1, num2) | Checks if a numeric value (num1) is greater than another (num2) and returns the logical value 'true' or 'false'. | greater(2, 3) | false |

Alternative: > | 2 > 3 | false | |

lessthan(num1, num2) | Checks if a numeric value (num1) is less than another (num2) and returns the logical value 'true' or 'false'. | lessthan(2, 3) | true |

Alternatively: | 2 < 3 | true | |

greatereq(num1, num2) | Checks whether a numeric value (num1) is greater than or equal to another (num2) and returns the logical value 'true' or 'false'. | greatereq(2, 3) | false |

Alternative: >= | 2 >= 3 | false | |

lessthaneq(num1, num2) | Checks whether a numeric value (num1) is less than or equal to another (num2) and returns the logical value 'true' or 'false'. | lessthaneq(2, 3) | true |

Alternative: <= | 2 <= 3 | true | |

equal(number1, number2) | Checks if two values (number1, number 2) are equal and returns the logical value 'true' or 'false'. | equal({Old price}, {New price}) | false |

Alternatively: = | {Old price}={New price} | false | |

unequal(number1, number2) | Checks whether two values (number1, number2) are not equal and returns the logical value 'true' or 'false'. | unequal({Old price}, {New price}) | true |

Alternative: | {Old price}<>{New price} | true | |

concatenate(string1, string2, ...) | Combines several character strings (string1, string 2, ...) into one character string. | concatenate({Supplier}, " has the product ", {Product}) | Microsoft has the product Windows |

Alternative: & | {Supplier} & " has the product " & {Product} | Microsoft has the product Windows |

### Mathematical functions

Operator | Description | Input | Result |
---|---|---|---|

abs(number) | Returns the absolute value of a number. | abs(-2) | 2 |

ceiling(number, significance) | Rounds a number to the nearest integer or to the nearest multiple of the specified significance. If either argument is non-numeric, the formula returns an empty value. | ceiling(2.14) | 3 |

If the number is an exact multiple of the significance, then no rounding occurs. If the number and the significance are negative, then the rounding is away from 0. If the number is negative and the significance is positive, then the rounding is towards 0. | ceiling(-2.14, 4) | 0 | |

even(number) | Assigns a real number to the nearest larger even number. | even(2.14) | 4 |

exp(number) | Exponential function for Euler's number e. Returns the value of e given high (number). | exp(1) | 2.71828... |

floor(number, significance) | Rounds a number to the nearest integer or to the nearest multiple of the specified significance. If either argument is non-numeric, the formula returns an empty value. | floor(2.86) | 2 |

If the number is an exact multiple of the significance, then no rounding takes place. If the sign of the number is positive, then the rounding is towards 0. If the sign of the number is negative, then the rounding is away from 0. | floor(-3.14, 5) | -5 | |

int(number) | Assigns the nearest smaller integer to a real number. | int(-3.14) | -4 |

lg(number) | Logarithm function (number) with 10 as base. | lg(100) | 2 |

ln(number) | Logarithm function (number) with Euler's number e as base. | In(e) | 1 |

log(number, base) | Logarithm function (number) with definable base. | log(81, 3) | 4 |

But if no base is given, this function works exactly like lg(), with 10 as base. | log(1000) | 3 | |

odd(number) | Assigns a real number to the nearest larger odd number. | odd(-2.14) | -1 |

round(number, digits) | Rounds a number to the nearest integer. If no decimal place (digits) is specified, the number is rounded to the 1st digit to the left of the decimal point. | round(3.14) | 3 |

If a positive decimal place (digits) is given, the digit to the right of the decimal point is rounded. | round(3.14, 1) | 3.1 | |

If a negative decimal place (digits) is given, is rounded to the left of the decimal point. | round(3.14, -3) | 0 | |

round(99.1, -2) | 100 | ||

rounddown(number, digits) | Rounds a number towards zero. If no decimal place (digits) is given, the number is rounded to the 1st digit left of the decimal point. | rounddown(3.12, 1) | 3.1 |

rounddown(-3.15) | -3 | ||

roundup(number, digits) | Rounds a number from zero to the nearest whole number. If no decimal place (digits) is given, the number is rounded to the 1st digit left of the decimal point. | roundup(3.12, 1) | 3.2 |

roundup(-3.15) | -4 | ||

sign(number) | Checks whether a number is greater, equal or less than 0. Returns the values 1, 0 and -1 respectively. In other words: it returns the sign of a number, for '+', 'zero' and '-' with 1, 0, and -1 respectively. | sign(-2) | -1 |

sqrt(number) | Returns the square root of a number. | sqrt(81) | 9 |

sum(num1, num2, ...) | Returns the sum of the numbers (num1, num2, ...) | sum(7,8,9) | 24 |

### Text functions

Operator | Description | Input | Result |
---|---|---|---|

exact(string1, string2) | Checks whether two character strings (string1, string2) are exactly identical. Returns the values 'true' or 'false' respectively. Case sensitive. | exact('SeaTable', 'Seatable') | false |

find(findString, sourceString, startPosition) | Returns the start position of a string (findString) within another string (sourceString). It is case sensitive. Without find, 0 is returned. If the start position (startPosition) is given as decimal, it is rounded down. If the cell in the column for the keyword (findString) is still empty, 1 is returned. If the cell in the column for the target string (sourceString) is still empty, an empty value ('') is returned. | find('Sea', 'seaTable', 1) | 0 |

The search will start from the given 'startPosition'. This 'startPosition' has no influence on the result: it always returns the absolute start position. If the 'startPosition' of the character string to be searched for (findString) is given after the actual start position of the character string (sourceString), 0 is returned, since nothing was found from this position. | find('table', 'big table', 4) | 5 | |

find('table', 'big table', 6) | 0 | ||

left(string, count) | Returns the specified number (count) of characters at the beginning of a string. | left('SeaTable', 3) | Sea |

len(string) | Returns the number of characters in a string. | len('SeaTable') | 8 |

lower(string) | Converts a character string to lower case letters. | lower('German) | German |

mid(string, startPosition, count) | Returns the specified number (count) of characters from the specified start position (startPosition) of a string. | mid('SeaTable is the best', 1, 8) | SeaTable |

Start position (startPosition) and count must not be empty, negative or zero. However, if start position (startPosition) and number (count) are given as decimal, they are rounded down. Too much count is ignored. | mid('SeaTable is the best.', 10.9, 27.3) | is the best. | |

replace(sourceString, startPosition, count, newString) | Replaces a part (count) of a character string (sourceString) from a certain start position (startPosition) with another character string (newString). The number (count) of characters is only taken into account for the old string (sourceString), but not for the new string (newString). | replace('SeaTable is the best.', 1, 8, 'Seafile') | Seafile is the best. |

If number (count) is given as zero, the new string (newString) is simply added to the old string (sourceString) from the start position (startPosition). | replace('SeaTable is the best.', 1, 0, 'Seafile') | SeafileSeaTable is the best. | |

rept(string, number) | Repeats a string as often (number) as specified. | rept('Sea ', 3) | Sea Sea Sea |

right(string, count) | Returns the specified number (count) of characters at the end of a string. | right('SeaTable', 5) | Table |

search(findString, sourceString, startPosition) | Returns the start position of a string (findString) within another string (sourceString). It is not case-sensitive. Without find, 0 is returned. If the start position (startPosition) is given as decimal, it is rounded down. If the cell in the column for the keyword (findString) is still empty, 1 is returned. If the cell in the column for the target string (sourceString) is still empty, an empty value ('') is returned. | search('Sea', 'seaTable', 1) | 1 |

The search will start from the given 'startPosition'. This 'startPosition' has no influence on the result: it always returns the absolute start position. If the 'startPosition' of the character string to be searched for (findString) is given after the actual start position of the character string (sourceString), 0 is returned, since nothing was found from this position. | search('table', 'big table', 4) | 5 | |

search('table', 'big table', 6) | 0 | ||

substitute(sourceString, oldString, newString, index) | Replaces existing text (oldString) with new text (newString) in a string (sourceString). If there is more than one text (oldString) in the string (sourceString), only the 'index'-th text is replaced. The text is case-sensitive. | substitute('SeaTableTable', 'Table', 'file', 1) | SeafileTable |

If the index is given as 0 or not, all found text (oldString) will be replaced by the new text (newString). | substitute('SeaTableTable', 'Table', 'file') | Seafilefile | |

T(value) | Checks whether a value is text. If so, the text is returned. If no, the return value is empty. | T(123) | (empty) |

T('123') | 123 | ||

text(number, format) | Converts a number into text and formats it in the specified format. The format can be percent and number as well as dollar, euro and yuan. | text(150, 'euro') | €150 |

When a number is converted directly to percent, its absolute value is retained. In other words, 50 is converted into 5000%. But if you want 50%, you have to divide the number by 100 before the conversion. | text(50, 'percent') | 5000% | |

text(50/100, 'percent') | 50% | ||

trim(string) | Removes spaces at the beginning and end of a string. | trim(' SeaTable ') | SeaTable |

upper(string) | Converts a string to uppercase letters. | upper('German) | GERMAN |

value(string) | Converts a text (string) representing a number into a number. | value('123') | 123 |

### Date functions

Operator | Description | Input | Result |
---|---|---|---|

+ | Adds days or durations to a date with or without minutes. If the date column has no minutes, 00:00 of that day is taken as the default value. | date(2021, 1, 31)+1 | 2021-02-01 |

For example, if a duration of 8 hours 20 minutes in the column {Duration} has: | date(2021, 1, 31)+{Dauer} | 2021-01-31 08:20 | |

- | Subtracts days or duration from a date with or without minutes. If the date column has no minutes, 00:00 is taken as the default value from that day. | date(2021, 1, 31) - 30 | 2021-01-01 |

For example, if a duration of 8 hours 20 minutes in the column {Duration} has: | date(2021, 1, 31)-{Dauer} | 2021-01-30 15:40 | |

Mit {Datum1}-{Datum2} gibt es den Zeitunterschied in Tagen zurück. | date(2021, 1, 31)-date(2020, 12, 23) | 39 | |

date(year, month, day) | Returns a date in international format (ISO) from entered year, month and day. If the year is entered with two digits, it is automatically understood as a year in the 1900s. If the number of the month or day is too large (greater than 12 or 31 respectively), these months or days are automatically converted to the next year or month. | date(2021, 1, 3) | 2021-01-03 |

dateAdd(date, count, unit) | Adds the specified number (count) of years ('years'), months ('months'), weeks ('weeks'), days ('days'), hours ('hours'), minutes ('minutes') or seconds ('seconds') to a date/time ('date'). | dateAdd('2020-02-03', 2, 'days') | 2020-02-05 |

Tip: if you want to add a complex duration (count) such as 1 day 12 hours, you can convert it to e.g. 24+12=36 hours ('hours') and enter it into the formula as a uniform duration (count). The duration is converted to the smallest unit: in this case, hours. | dateAdd('2020-09-04 13:05:18', 36, 'hours') ODER dateAdd({form submission}, 36, 'hours') | 2020-09-06 01:05:18 | |

datedif(startDate, endDate, unit) | Calculates the seconds, days, months, or years between two date values. The optional unit argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years). | dateDif('2018-01-01', '2020-01-01') | 2 |

The optional unit argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years). | dateDif('2019-10-11', '2020-12-12', 'M') | 14 | |

day(date) | Returns the day of a date as a number. The returned number is between 1 and 31. | day('2020-01-03) | 3 |

days(startDate, endDate) | Returns the number of days between two dates (startDate and endDate). | days('2020-01-01', '2020-02-03') | 33 |

eomonth(startDate, months) | Determines the date of the last day of the month that is the specified number (months) of months after the specified date (startDate). If the number (months) is given as 0, the last day of the month is simply determined. | eomonth('2020-01-01', 1) | 2020-02-29 |

If the number (months) is given as negative, the date of the last day of the month that contains the absolute number (months) of months before the specified date (startDate) is determined. | eomonth('2020-01-01', -1) | 2019-12-31 | |

hour(date) | Returns the hour of a date as a number. The number returned is between 0 and 23. | hour('2020-02-14 13:14:52) | 13 |

If no hour is contained in the time specification (date), 0 is returned. | hour('2020-02-14) | 0 | |

hours(startDate, endDate) | Returns the number of hours between two date values (startDate and endDate). The minutes in the date values are not taken into account. | hours('2020-02-14 13:14', '2020-02-14 15:14') | 2 |

If no hours are included in the time specification (startDate or endDate), 0 o'clock on this day is automatically assumed. | hours('2020-02-14', '2020-02-14 15:14') | 15 | |

minute(date) | Returns the minutes of a time specification (date) as a number. The number returned is between 0 and 59. | minute('2020-02-14 13:14:52) | 14 |

If no minutes are included in the time (date), 0 is returned. | minute('2020-02-14) | 0 | |

month(date) | Returns the month of a date as a number. The returned number is between 1 (January) and 12 (December). | month('2020-02-14 13:14:52) | 2 |

months(startDate, endDate) | Returns the number of months between two date values (startDate and endDate). The days and time in the date values are not taken into account. | months('2020-02-01 13:14', '2020-03-31 15:54') | 1 |

If no month is given in the date values (startDate, endDate), January is automatically assumed to be the month. | months('2020', '2021' | 12 | |

networkdays(startDate, endDate, holiday1, holiday2, ...) | Returns the number of full working days between two dates (startDate and endDate). You can also define holidays other than Saturday and Sunday (holiday1, holiday2, etc.), which are also deducted. If you do not want to include public holidays, you can simply omit these parameters. | networkdays('2020-01-01', '2020-01-07','2020-01-01') | 4 |

Please note that the specified last day (endDate) is also included in the formula. That means, as in this formula, three working days are counted: the 7th, 8th and 9th of September, 2020. | networkdays('2020-09-07', '2020-09-09') | 3 | |

now() | Returns the current date and time. This column is only updated automatically when the Base is reloaded. | now() | 2020-09-07 12:59 |

second(date) | Returns the seconds of a time (date) as a number. The number returned is between 0 and 59. | second('2020-02-14 13:14:52') | 52 |

today() | Returns the current date. This column is only updated automatically if the Base has been reloaded. | today() | 2020-09-07 |

This function is handy for calculating time between a certain date & time and now. On each reload or recalculation of the Base, the calculation is updated. | networkdays('2020-09-01', today()) | 4 | |

weekday(date, weekStart) | Returns the weekday of a date as a number. The returned number between 1 and 7, where you can define the first day of the week (weekStart): Monday ('Monday') or Sunday ('Sunday' or omitted, since the start as Sunday is the default). A third option is not possible. Upper/lower case is not considered. | weekday('2020-01-01', 'Monday') | 3 |

If no 'weekStart' is given or if a 'weekStart' other than 'Monday' or 'Sunday' is given, it is always assumed to be 'Sunday'. So if it should be 'Monday', enter 'Monday'; if it should be 'Sunday', you can omit this parameter. | weekday('2020-01-01', 'Thursday') OR weekday('2020-01-01') | 4 | |

weeknum(date, return_type) | Returns the absolute week number of a date as a number. The returned number is between 1 and 53, where you can define the first day of the week (return_type). Enter the number 1 or 2, or 11 to 17, and 21 as "return_type" to define the start of a week: 1/Sunday、2/Monday、11/Monday、12/Tuesday、13/Wednesday、14/Thursday、15/Friday、16/Saturday、17/Sunday. If you want the week number to be returned according to ISO standard, specify the number of 21 as "return_type", or use the function isoweeknum. | weeknum('2020-01-12', 11) | 2 |

If no 'return_type' is given, it is always assumed to be 'Sunday'. | weeknum('2020-01-12') | 3 | |

isoweeknum(date) | Returns the ISO week number (KW) of a date as a number. | isoweeknum('2021-01-04') | 1 |

Different than the function weeknum(): with the function isoweeknum() you sometimes also get a week number which is still in the previous year. In this example: the 1st of January 2021 belongs to the 53rd calendar week of the year 2020 according to the ISO standard. | isoweeknum('2021-01-01') | 53 | |

year(date) | Returns the year of a date as a number. | year('2020-01-01') | 2020 |

### Logical functions

Operator | Description | Input | Result |
---|---|---|---|

and(logical1, logical2, ...) | Checks if all arguments (logical1, logical2, ...) are true (valid, not empty and not equal to zero). If yes, 'true' is returned, otherwise 'false'. | and(1, '', 2) | false |

if(logical, value1, value2) | Checks if an argument (logical) is true and if yes, returns the first value (value1) and if no, returns the second value (value2). | if(1>2, 3, 4) | 4 |

For the condition (logical) only a comparison with is allowed. If you enter only condition (logical) and the first value (value1): it will return the first value (value1) if the condition (logical) is true; and it will return an empty value ('') if the condition (logical) is false. | if({Budget}>{Preis}, 'Ja') | Yes | |

ifs(logical1, value1, logical2, value2, ...) | Checks if one or more conditions (logical1, logical2, ...) are true and returns a value (value1, value2, ...) that matches the first TRUE condition. | ifs( 1>2, 3, 5>4, 9) | 9 |

not(boolean) | Inverts the logical value (boolean). In other words: converts 'true' to 'false' and 'false' to 'true'. | not(and(1, '', 2)) | true |

or(logical1, logical2, ...) | Checks if at least 1 of the arguments (value1, value2, ...) is true (valid, not empty and not equal to zero), and returns 'true' in this case. If all arguments are false, then returns 'false'. | or(1, '', 2) | true |

switch(logical, matcher1, value1, matcher2, value2, ..., default) | Evaluates an expression (logical) against a list of values (matcher) and returns the result (value) corresponding to the first matching value. If there is no match, an optional default value is returned. At least 3 parameters (logical, matcher, value) must be specified. | switch({Noten}, 1, 'Sehr gut', 2, 'gut', 3, 'befriedigend', 4, 'bestanden', 'nicht bestanden') | Very good |

If there are several identical values in the value list (matcher), only the first hit is taken into account. | switch(int(68/10), 6, 'OK', 6, 'KO') | OK | |

xor(logical1, logical2, ...) | Returns the contravalence of all arguments. In other words, checks if the number of true arguments is (logical) odd and returns 'true'. | xor(1, 0, 2<1, | false |

### Statistical functions

Operator | Description | Input | Result |
---|---|---|---|

average(number1, number2, ...) | Returns the average of the numbers (number1, number2, ...) | average(1, 2, 3, 4, 5) | 3 |

count(number1, number2, ...) | Returns the number of numbers (number1, number2, ...) Only valid and empty numbers are considered. In this example, '' is an empty value, and '3' is not a number, but a text. | count(1, '', 2, '3') | 2 |

counta(textORnumber1, textORnumber2, ...) | Counts the number of non-empty cells (textORnumber1, textORnumber2, ...). These cells can be text or numbers. In this example, 1 and 2 are numbers, '3' is text, and '' is an empty value. | counta(1, '', 2, '3') | 3 |

countall(textORnumber1, textORnumber2, ...) | Counts the number of elements (textORnumber1, textORnumber2, ...) including numbers (1, 2), text ('3') and empty cells (''). | countall(1, '', 2, '3') | 4 |

countblank(textORnumber1, textORnumber2, ...) | Counts the number of empty cells. | countall(1, '', 2, '3') | 1 |

max(number1, number2, ...) | Returns the largest value from the specified numbers (number1, number2, ...). | max(-1, 3, 4, 2, -1) | 4 |

min(number1, number2, ...) | Returns the smallest value from the specified numbers (number1, number2, ...). | min(-1, 3, 4, 2, -1) | -1 |

### Link functions

Operator | Description |
---|---|

{link}.{columnName} | Returns the value from the column {columnName} of a linked row in another table. You also specify the name of the cell in the current table ({link}), which has a link to another table. If multiple rows are linked, the value of the first row is returned. |

rollup({link}, {columnName}, summary, [condition]) | Returns a calculated value (summary) of concatenated rows (columnName) in other tables. Calculations that can be performed are 'average', 'concatenate', 'count', 'max', 'min' and 'sum'. With the input from the linking column (link), SeaTable will search for values in the linked table in the column to be searched (columnName). The optional condition can be used to filter values. |

lookup(findmax({link}, {comparisonColumn}), columnName) | Use a lookup() to display a field from this entry. The function will then be displayed in the field specified with the {link} column linked table, in which {comparisonColumn} column to find the largest value, and then returns the value in the same row from the {columnName} column back. |

lookup(findmin({link}, {comparisonColumn}), columnName) | Use a lookup() to display a field from this entry. The function will then be displayed in the field specified with the {link} column linked table, in which {comparisonColumn} column to find the smallest value, and then returns the value in the same row from the {columnName} column back. |

countlinks("columnName") | Returns the number of linked articles in the "Link" column (columnName). |