Commonly Used 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.

Calculate duration in days, hours, minutes or seconds

You can retrieve the duration between two dates in days, hours, minutes, or seconds.

For example:

(due - start).days

It will return the number of days between the two dates.

Note: The result is rounded up to the nearest whole number (the smallest integer greater than or equal to the actual value). For instance, a duration of one hour will be counted as one day.

If you prefer a more precise calculation, you can adjust the expression, for example:

(due - start).hours / 24

Retrieve specific information from a start or due date

You can extract specific components of a date—such as the year, month, day, hour, minute, second, or weekday—from a start or due date.

For example, due.weekday returns the weekday of the due date.

Note: The weekday value follows a numerical format where 1 = Monday, 2 = Tuesday, and so on.

Calculate the numbers of work days between two dates

You can use formulas to determine the number of workdays between two dates.

Use the following formula:

WORKDAYS(start, due)

If the start and due dates are the same, the result will be 1 (day).

If you type the below formula:

WORKDAYS(<today>, <tomorrow>)

This will return 2 (days).

By default, weekends are set according to your account settings. If you would like to customize the weekends, you can specify different weekend days using:

WORKDAYS(start, due, weekend)

Replace weekend with the corresponding number for your selected days:

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.