Function computation
1. Function introduction
Function computation is another way of assigning default values.
The system provides a variety of function formulas for processing data, and users can automatically calculate the results, just like the function functions in Excel.
2. Supported functions
Currently, the system supports mathematical, date, text, and logical functions.
2.1 Mathematical functions
SUM summation of values
Calculates the sum of numbers
Example: SUM(10,20,30), result: 60
Calculates the sum of 10,20,30
SUM(value1,value2...)
AVERAGE mean value
Computes the mean of the parameters
Example: =AVERAGE(10,20,30) , result: 20
Calculates the average of 10,20,30
AVERAGE(value1,value2...)
PRODUCT multiplied values
Returns the product of multiplying two numbers
Example: =PRODUCT(15,4), Result: 60
Calculates the product of 15 times 4
PRODUCT(value1,value2...)
MOD remainder value
Returns the remainder when dividing two numbers
Example: =MOD(15,4), result: 3
Computes the remainder when dividing 15 by 4
MOD(dividend, divisor)
COUNTA count of non-null values
Calculates the number of non-null values in the parameter
Example: =COUNTA(January,February,March), Result: 2
Calculates the number of completed indicators in the first quarter. Three checkboxes in the form correspond to whether the indicators in January, February, and March are completed. The names are January, February, and March, and February is incomplete.
COUNTA(value1,value2...)
COUNTARRY count of objects
Counts the number of members, departments, multiple selections, subforms, or related forms
Example: =COUNTARRAY(process), result: 7
Calculates the number of subforms named "Process" (actually, there are 7 processes)
COUNTARRAY (array class field)
MIN minimum value
Returns the smallest value in a set of numbers
Example: =MIN(10,20,30) , result: 10
Returns the smallest number among 10,20,30
MIN(value1,value2...)
MAX maximum value
Returns the largest value in a set of numbers
Example: =MAX(10,20,30) , result: 30
Returns the largest number among 10,20,30
MAX(value1,value2...)
ABS absolute value
Calculates the absolute value of a number
Example: =ABS(-7) , result: 7
find the absolute value of -7
ABS (value)
INT integer
Returns the nearest integer that is always less than or equal to the original number
Example: =INT(-3.14159265), result: -4
round -3.14159265
INT (number)
ROUND rounding to the specific decimal places
Rounds a number to a specified number of digits
Example: =ROUND(3.14159265,4) , result: 3.1416
3.14159265 to four decimal places
ROUND(number, number of digits)
ROUNDUP rounding to increase the absolute value
Rounds a number by the specified number of digits in the direction of increasing absolute value
Example: =ROUNDUP(3.14159265,4), result: 3.1416
3.14159265 to four decimal places
ROUNDUP(number, number of digits)
ROUNDDOWNrounding to decrease absolute value
Rounds a number by the specified number of digits in the direction of decreasing absolute value
Example: =ROUNDDOWN(3.14159265,4) , Result: 3.1416
3.14159265 to four decimal places
ROUNDDOWN(number, number of digits)
2.2 Date functions
MINUTE returning the number of minutes
Returns the minutes in Time; the return value range is between 0 - 59
Example: =MINUTE('2023-5-1 11:59') , result: 59
Returns the minute at 2023-5-1 11:59
MINUTE(datetime/time)
HOUR returning the number of hours
Returns the number of hours in the Time; the return value range is between 0 - 23
Example: =HOUR('2023-5-1 11:59') , result: 11
Returns the hour of 2023-5-1 11:59
HOUR(datetime/time)
WEEKDAY returning the day of the week
Returns the date as the day of the week; the return value range is between 1 - 7
Example: =WEEKDAY('2023-5-1') , result: 1
Returns the hour of 2023-5-1 11:59
WEEKDAY(datetime)
DAY returning the day
Returns the number of days in the date; the return value range is between 1 - 31
Example: =DAY('2023-5-1') , result: 1
Returns the number of days to 2023-5-1
DAY (date time)
MONTH returning the month
Returns the month of the date, the return value range is between 1 - 12
Example: =MONTH('2023-5-1') , result: 5
Returns the month of 2023-5-1
MONTH (datetime)
YEAR returning the year
Returns the four-digit year of the date
Example: =YEAR('2023-5-1') , result: 2023
Returns the year of 2023-5-1
YEAR(datetime)
DATENOW returning the current time
Returns the current time
Example: =DATENOW() , result: 2023-01-01 12:00
DATE NOW()
DATEADD adding time to date
Adds a certain time period to a date and then formats the calculation result
Example: =DATEADD("2008-11-11 12:23",8,"h","DATE") , the result: 2008-11-11 20:23
Finds the time point after 8 hours after 2008-11-11 12:23, and the result keeps the date and time format
DATEADD(initial date, increasing duration, [duration type], [output format])
Increases duration: means adding a duration
Duration type: 'y' stands for year, 'M' stands for month, 'd' stands for day, 'h' stands for an hour, and 'm' stands for a minute; the default is d days
[Output format]: 'DATE' represents the date, 'DATETIME' is the date and time; if this parameter is not specified, the default is the type DATE
DATESUBTRACT minusing time from the date
Subtracts a certain time period from a date, and then format the calculation result
Example: =DATESUBTRACT("2008-11-11 12:23",8,"h","DATETIME") , the result: 2008-11-11 4:23
Finds the time point before 2008-11-11 12:23 8 hours ago, and the result keeps the date and time format
DATESUBTRACT(initial date, increasing duration, [duration type], [output format])
● Decreases duration: means reduce the duration by one
● Duration type: 'y' stands for year, 'M' stands for month, 'd' stands for day, 'h' stands for an hour, and 'm' stands for a minute; the default is d days
● [Output format]: 'DATE' represents the date, 'DATETIME' is the date time; if this parameter is not specified, the default is the type DATE
DATEIF duration between two dates
Calculates the length of time between two dates/times to the nearest year, month, day, hour, or minute
Example: =DATEIF("2021-3-8","2021-3-14",2,"d") , result: 7 days
Calculates the duration from 2021-3-8 to 2021-3-14, accurate to the day
DATEIF(start, end, format method, [output unit])
Formatting method: 1-start date 00:00 end date 00:00, 2-start date 00:00 end date 24:00
Output unit: 'y'-year; 'M'-month; 'd'-day; 'h'-hour; 'm'-minute; if this parameter is not specified, it defaults to 'd'
2.3 Logical functions
IF conditional statements
Sets the judgment condition and then returns different text according to the judgment result TRUE or FALSE
Example: =IF(Score>=60,"Pass","Fail"), Result: "Pass"
The score will be recorded as a pass if it is greater than or equal to 60 points. Otherwise, it will be recorded as a fail
IF(expression, output when true, output when false)
Regardless of whether the expression is true or not, the output result is fixed as a text type
2.4 Text functions
CONCAT merging texts
Combines two or more texts into one
Example: =CONCAT("aaaa","2021MMDD","bbbb") , result: aaaa2021MMDDbbbb
Combines aaaa, 2021MMDD, and bbbb three paragraphs of text
CONCAT(text1,text2...)
3. Configuration methods
Method 1: Function, Field
In the Edit Function, set the function first, then select the calculation field; there is no need to use +-*/ such calculation symbols
Method 2: Fields and Calculation symbols
In the Edit Function, there is no need for functions, instead use the custom method, directly select the field, and enter the +-*/ ( ) calculation symbol
