# Formula Explained

## Writing a formula

In the Free subscription plan, you can add one formula to the custom field for each project. Upgrade your subscription plan to add more formulas. More information can be found on our pricing page.

Formula lets you take an existing value for your tasks and output something new. You can reference a field by its name and do basic or advanced calculations to return the value you would like to see.

For example, if you want to divide the time spent for your task by 2, simply type something like below in the formula input. Quire will calculate the time spent for each task and return the value in the custom field.

``````timeSpent/2
``````

## Performing calculations

You can do basic calculations using formulas, including addition (+), subtraction (-), multiplication (*) and division (/). For example, if you want to calculate the duration of your task, you can subtract the start date from the due date. Try typing something like this:

``````due - start
``````

Tip: You can add parentheses `( )` to override the original precedence. For example, if you type something like `(due - start) *2`, then the due date and start date subtraction will precede the multiplication of 2.

Another quick way to do calculations in Quire is to use the functions, for example, you can calculate the sum of the time spent and the estimated time of a task by typing something like this:

``````SUM(timeSpent, estimated)
``````

You can even use different types of operators, such as the comparison operators to display desired information. For example, if you want to check whether the task has a priority higher than or equal to high, then you can type something like this:

``````priority >= high
``````

Learn all the different operators and functions you can use in the Quire formulas.

## Formula writing tips

#### Use the dot operator to call out a specific value

In the formula, you can use the `.` between different fields for a set of possible returns. For example, if you want to list out all the name of the subtasks, then you can type something like this:

``````subtasks.name
``````

Another example would be, if you want the system to generate the priority of the parent task, then you can type something like this:

``````parent.priority
``````

Tip: We have listed out all the different fields that you can specify .

#### Wrap field names that include spaces or special characters with brackets

When naming your custom field names, you need to avoid using these following characters: quotation marks ("), backslash (\) and curly brackets (}).

However, if you are referring to them in the formulas, only alphabets (A-Z), numbers (0-9) and underscore (_) are recognized. You need to wrap the field names that include spaces or other characters that are not mentioned above in curly brackets `{` `}`.

For example, if you added a new field named Cost, typing the below formulas means the same thing and will return the same results.

``````Cost *2
``````
``````{Cost} *2
``````

But if you have a custom field named Schedule Variance which in this case has a space between these two words, when you referencing it in the formula, you must type something like below, wrapping them with the curly brackets `{` `}`.

``````{Schedule Variance} *2
``````

Note: The cases are insensitive, so typing something like `cost` or `Cost` means the same thing.

#### Show text in the formula results

If you want there to be text shown in the results of a formula, you will need to always wrap it in quotation marks `"` or `'`.

For example, if you added two custom fields, one for the item ID, and another for the item name. You can type in something like below to put texts (in this case, the colon) in the results.

``````id + ":" + name
``````

#### Specify a particular member/task/project in a formula

You can use the member identifier `@member_username`, task identifier `#task_id` or the project identifier `#project_id` in your formula to call out a specific value.

For example, if you want to call out the due date of a particular task, you can type something like below. And it will return the due date of the task with the ID#23 in the field.

``````#23.due
``````

Note: Please make sure that there are not any spaces between `#` and the task ID, or else the identifier will not work correctly.

Here’s another example, if you want to know how much time the entire project has spent, you can type something like this in the Organization Overview description:

``````SUM(#project_id.tasks.timeSpent)
``````

Note:

• The project identifier `#project_id` can only be used in the Organization Overview’s description area. It cannot be used at the task’s or project’s description.
• If the user’s username ID or project ID contains `.`, `-` or `_`, you will need to put either of them between curly brackets for the identifier to work correctly. For example, the project ID “project-12” needs to be put between curly brackets like this: `#{project-12}`.

## Literal values in your formulas

Literal values are the string of texts or numbers that won’t be changed in the formulas. In Quire’s formulas, we support different formats for different literal values.

• Numbers: `123`, `-1.23`
• Texts: `"text"`, `'text'`
• List: `[1,2,3]`
• Boolean: `true`, `false`
• Duration: `12:01`, `123:12:23`, `2h 30m`, `2d 5s`, `30m`
• Dates: `<2022/12/25>`, `<12/23>`, `<23>`, `<2022/01/23 13:10:00>`, `<2023/12/25 16:00>`, `<15:15>`
• Days: `<today>`, `<today-1>`, `<tomorrow+3 13:10:05>`, `<+5>`

Note:

• For texts, you can connect two strings of text by typing `'first' "second"`. It is the same as `'first second'`.
• Days are also supported in different languages. For example, if you are using the German language, you can type `<Morgen>`.
• For days, `<+5>` equals `<today+5>`.
• For duration, hours can only be in 0-23 and the syntaxes are `hh:mm:ss` or `mm:ss`.

## Formatting the result of the formula

You can choose the format you would like to apply to the results of your formula by clicking on the Format tab when adding or editing a formula. ## Useful formula examples

Here are some real world examples to demonstrate how you can use the Quire formulas to build the desired outcome that you are looking for.

If you want to calculate the total cost of a task including the total cost of its subtasks, you will need to create 2 separate custom fields. The first field will be your `Cost` field. Set the field type to `Number` and `Decimals` set to 2.

Then create another custom field and name it `Total Cost`. Set the field type to Formula and type something like this:

``````SUM(subtasks.{Total Cost}, Cost)
``````

Learn all the functions you can use in the Quire formulas.

#### Display multiple values in one field

If you want to list out a set of items or values in one field, you can use an array to achieve that. An array is simply a collection of items and the items can be texts or numbers, etc.

For example, if you type something like below in your formula, it will return the two specified tasks.

``````[#2, #23]
``````

Tip: You can specified tasks with the task identifier `#task_ID` in your formula.

With an array formula, you can even perform a more complex calculation on one or more items instead of doing the calculations one by one.

For example, you can type something like below and it will return the due date of the two specified tasks.

``````[#2, #23].due
``````

This formula equals to `[#2.due, #23.due]`, but you can see how much more convenient it is to use array formulas so you don’t have to type `due` multiple times.

Learn more about Quire’s array formulas that allows you to list out or do calculations to a collection of data.

#### Create a conditional formula

You can use a conditional operator `?:` to perform a calculation when a field contains a specific value. This is similar to the IF function in Excel. You can have two results, it will show the first result if the condition is true and show the second result if it’s false.

For example, if you want to display a red indicator (🔴) when the total time spent for a task is over 5 hours and if not, show the green one (🟢), you can type something like this:

``````timeSpent > 5h ? "🔴" : "🟢"
``````

In the above formula, `timeSpent > 5h` in front of the `?` is the condition and the `"🔴"``"🟢"` on the both sides of the colon are the results. Returning the first result if the condition is true and returning another if it’s false.

#### Sort and order multiple values

If you would like to sort and order a collection of items or values, you can simply use the SORT function.

For example, if you have 4 subtasks and their due dates are Sep 1, Aug 23, Sep 1, Jul 30 respectively. Typing something like below will sort the subtasks’ due dates from earliest to latest and list out all the due dates in the field.

``````SORT(subtasks.due)
``````

The return results will be :

``````Jul 30, Aug 23, Sep 1, Sep 1
``````

You can see in the result that there are two subtasks with the same due date (Sep 1). In this case, you can add the DISTINCT function to the above formula to remove the duplicated results.

Try typing something like this:

``````DISTINCT(SORT(subtasks.due))
``````

Then the field’s results return:

``````Jul 30, Aug 23, Sep 1
``````

However, if you would like to display the subtasks’ names in the order of their due dates from earliest to latest, you will need to use the `order by` operator.

Type something like this:

``````subtasks order by any.due
``````

Basically what this formula will do is search for the subtasks and order them by their due dates.

Note: You can use the `any` identifier with the `order by` operator to specify the field you would like the order to be in.

Learn all the different operators and functions you can use in the Quire formulas.

Last updated on