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 

Learn how to create a custom field to add your formulas.

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 custom field names in the formulas, you need to wrap the field names that include spaces or other characters (including @, #, &, etc) 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/tag/project in a formula

You can use the member identifier @member_username, task identifier #task_id, tag identifier #tag_name 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)

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.

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

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.

You can color-code the formula results based on conditions that are defined by you. The fields that meet the rules will automatically receive the specified color.

Click Add a new condition, then enter the conditions and select the colors. Lastly, remember to Save the conditions.

Note: The colors will be applied to the field results based on the order of the conditions. The condition that is placed at the top will be applied to the field results first. You can drag and drop the conditions by clicking on the six-dotted icon in front of the conditions to rearrange the order.

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.

Calculate the total cost of your tasks

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.