How do I change the format of a formula field?
Double-click the name of the formula field and select a format under Field format. Supported formats include integers, decimals, thousands separators, percentages, currencies, and dates.
250px|700px|reset
When the result of a TEXT calculation is a number, why do I get errors after using it in other functions?
Results of the TEXT function are all in text format. You can use the SUM or ABS function to convert them into numbers before using them in other calculations.
250px|700px|reset
Why does it say my formula is invalid?
Possible reasons could be:
- Symbols or brackets used in the formula aren't English characters. For example: “text” should be written as "text".
- There's an odd number of brackets. For example: IF(TRUE, 0, 1)) should be IF(TRUE, 0, 1).
- The formula contains invalid data. For example: in SUM([TextA]), "TextA" was deleted or doesn't exist.
- Percentages are incorrectly formatted. When performing calculations, percentages should be written as decimals. For example: 0.01.
- Incorrect symbols/operators were used.
- The data format is incorrect. If syncing is used, the data format needs to be changed before syncing.
What are some frequently used operators in formulas?
What is CurrentValue?
CurrentValue is an important argument for FILTER, SUMIF, and COUNTIF functions, referring to each value used in a calculation.
Why is the summation result incorrect?
- You can only sum data in formula and numeric fields. Please double-check if the fields you are summing contain text fields.
- When you require high data accuracy, please adjust the number of decimal places to 4, then re-check the calculation result.
How do I express percentages in formulas?
In a formula, percentages need to be written as decimals to be used in calculations. For example: 10% should be written as 0.10 in a formula.
What is the format of calculation results of date functions in formula fields?
The calculation results of date functions in formula fields are stored in timestamp format. Although you can change the display format to date in field settings, this only affects what is displayed and will not change the data format itself. The data is still stored in the timestamp format.
Therefore, the system will still recognize such data as timestamps. For example, when filtering this field, the filtering conditions are the same as those for numbers, not dates.
How do I use a formula to turn text into a date?
You can turn text into a date by using the TODATE function. For example: When calculating total sales for a certain time period, you need to convert the text to time in the formula.
Example: [Sheet].FILTER(CurrentValue.[Time] > TODATE( 2021/12/31 ) && CurrentValue.[Time] < TODATE( 2022/12/31 ))
Note: Text in the formula must be enclosed with double quotation marks.
When using the & symbol to concatenate, why are dates shown as numbers?
If you use & to concatenate dates, the results will be numbers. You can use the TEXT function to turn dates into text first to keep the date format in the result.
250px|700px|reset
The calculation result is supposed to be a date; why is it shown as a number?
Numbers are calculated values of date format. If the format of the calculation result is not what you want, double-click the result field and adjust the format to date. You can also change it to currency, percentage, and so on as needed.
250px|700px|reset
Note: When using date-related functions in formula fields, the calculation results are actually stored in timestamp format, not in date format. Even if you choose to display the result in date format, the system will still recognize the field as a timestamp.
How do I express blanks in formulas?
You can use =, or you can use the ISBLANK function. For example: IF([Date]="","Blank", "Not blank") or IF([Date].ISBLANK(),="","Blank","Not blank").
How should the date in the formula be written and used in the calculation?
There are two cases:
- When the field type of the data used in the formula is Date, they can be calculated directly. For example, in the following screenshot, data in End Date and Start Date are already both in date format, then you can directly use the former to minus the latter to calculate how many days are in between.
250px|700px|reset
- When a date is written in fixed-text format, you'll need to convert its field type to date using the TODATE function. For example, in the following screenshot, 2022/06/01 is written as text. In order to use it in formula calculation, you'll need to convert it with the TODATE function first.
- Note: Text in the formula must be enclosed with double quotation marks.
250px|700px|reset
What types of customized formats are available for the TEXT function?
Please see below for frequently used formats:
Given a date, how can we calculate the date after a specific number of working days from that date?
You can use the WORKDAY(start_date, num_days, [holidays]) function to calculate the date after a specific number of working days from a given date. For example, to calculate the date 10 working days after 2024/11/01, the function will look like this: WORKDAY("2024/11/01", 10).
By default, Saturdays and Sundays are considered non-working days. If you need to include other holidays, you can define them through the [holidays] argument, which should be an array containing dates. You can pre-define a date field and set it as the [holidays] argument.
How can I quickly count the number of values in a field using formulas?
You can use the COUNTA(value1, [value2,...]) function to count the number of non-empty values in a dataset. For example, COUNTA([person field]) counts the number of people in the person field and COUNTA([attachment field]) counts the number of attachments in the attachment field. If you enter multiple arguments, COUNTA will count the total number of values for all the arguments.
How many fields can be referenced in a formula field in Base?
A maximum of 300 fields can be referenced in a single formula field.
How can I search for duplicate fields across tables in Base?
You can use a formula field to find duplicate fields across tables. Use the following formula and replace the table and field names in the formula with the table and field names you need to check:
IF([Table 2].CountIf(CurrentValue.[Field Name in Table 2]=[Field Name in Current Table]) = 0, "Not duplicate", "Duplicate")
This formula will mark the results as "Duplicate" and "Not Duplicate".