Formula custom field with Advanced Editor

Thanks Richard! The percentage formatting for formulas is what I was most curious about. That’s what I need, as I am dividing two whole numbers and need the result to show as a percentage (particularly for reporting on the Dashboard view of the project). For now, I’ll have to continue with my napkin math to arrive at the result. Are format options for formulas on the roadmap?

Thanks again!

@unikornz , understood. Asana does not make their roadmap public but I’m fairly sure they are working on more updates to formulas and hopefully a ‘Percent’ function would be ideally added so that the results are displayed with the % symbol. The only way to get the result of a formula as a percentage (with a % symbol) is if ALL the number fields, used in the formula, are formatted as percentages.

However, I don’t see why you cannot use the formula as is for now, as long as the Formula field is titled ‘Percent (%)’, that would be clear enough, right? And in your Dashboard’s charts, make sure to title the chart accordingly. That’s surely better than manually calculating and inserting the result yourself into a percentage number field, no?

It definitely eliminates the manual work. I’ll give it a shot. Thanks for all the help, Richard!

1 Like

hi! I have the enterprise license but for me it only shows selected date columns when I select field 1 and field 2 and not the columns I want to create the calculated field. Both columns are numeric so ideally I should be able to see them. any ideas?

sorry I cannot post a screen cap!

Hi @Riddhiman_Sherlekar , welcome to the forum :wave:

Hmm, that’s strange. Are you sure your fields are added to the project where you are trying to create your formula field, and they are not just in your field library?

Even if you try to add the formula field to your library, you should still see a pop-up asking you to add any fields that are not already in your library, like this:

Is this what you are seeing? Or you are NOT seeing your fields at all in the list?

If so, try going to your project’s ‘Customize’ menu (top right button) and check under ‘Fields’, and note the icons next to your fields.

Do you see a ‘#’ or an ‘A’ next to them? If you see an ‘A’ that means you have created text fields instead of number fields, so you will need to recreate your fields as number fields.

Hi all! Richard here, an Asana Solutions Partner (consultant and trainer) and also part of the amazing Asana Forum team. :wave:

I’m happy to announce that Formula Chaining will be rolling out to all Business and Enterprise tiers today!

What is Formula Chaining?

With this update, you will be able to use Formula custom fields as inputs to build another Formula custom field using the Basic and Advanced Editor.

Let’s assume you have the following:
Formula 1 = Custom field A + Custom Field B
Formula 2 = Custom field A * Custom Field C

So with the Basic formula editor you will be able to create a new ‘Formula 3’ which is made up of Formula 1 + Formula 2 (or you can also use subtract, multiply or divide).

Using the Advanced Editor you could also include custom fields and number into the mix, such as ‘Formula 3’ divided by ‘Custom Field D’ and eg. multiplied by a number value.

A real-life example:

  1. Formula A ‘Subtotal’ = Cost per item * Quantity
  2. Formula B ‘20% TAX’ = Formula A ‘Subtotal’ * 0.20
  3. Formula C ‘Total’ = Formula A ‘Subtotal’ + Formula B ‘20% TAX’

:bulb: Notes

For the following, ‘Local’ refers to a custom field which is added only to one project, whereas ‘Global’ refers to custom fields which are added to the custom field library.

  1. Global formulas can only use global custom fields as inputs.
  2. If you add a global formula to a project, Asana will require all input custom fields to also be added to the project (if they haven’t already been added, you will be prompted to add them).
  3. When you create a local Formula custom field in a project:
    a) Local and global formula custom fields can be used as inputs
    b) If using a global formula custom field, its input fields should also be added to the project
  4. When you create a global Formula custom field in a project:
    a) Only global Formula custom fields can be used.
    b) If using a global formula custom field, its input fields should also be added to the project

:arrow_up: Limitations

  1. You can use up to 10 existing formulas within a Formula custom field (using the advanced editor).
  2. The number of Formula custom fields within a project is subject to the current limit of 100 custom fields (of any type) within a project.
  3. You will be able to use up to two layers of chains. You will not be able to create a formula custom field with another formula custom field as an input, if that formula is also made up of one or more formulas. For example:
    a) Possible:
    Formula 1 = Custom field A + Custom field B
    Formula 2 = Formula 1 / Custom field C (1st layer)
    Formula 3 = Formula 1 - Formula 2 (2nd layer)
    b) Not possible to use Formula 3 as an input in another formula, such as:
    Formula 4 = Formula 1 * Formula 2 * Formula 3 (3rd layer)
    c) To overcome this limitation, you could simply create Formula 4 by rebuilding Formula 3 into it, as per below:
    Formula 4 = Formula 1 * Formula 2 * (Formula 1 - Formula 2)

:question: Q&A

Will Formula Chaining be available for formula custom fields in Portfolios?
Yes!

When will this roll out?
Gradually over the coming days. Tip: refresh your browser from time to time, or close and reopen to the Asana desktop of if you use that instead.

Which Asana tiers will this be available to?
Business and Enterprise, of which, Formula fields are already available to.

How many formula fields can I include within a formula field?
10. Didn’t you read the limitations section above? :sweat_smile:

How many formulas can I chain?
Two layers. Come on, now I’m just repeating myself. :stuck_out_tongue_winking_eye:

Looking forward to hearing what you think of this update and how you will be using it.
Happy chaining!

7 Likes

Finally :smiley: !!!

Is there a way to tie a formula to a task and a changed state? For instance, a rule that when task A is marked complete, then run custom formula X and Z. Thanks!

Hi @John_Bates , I’m not quite sure if I’m following what you mean.

Formula fields are dynamically updated whenever their input fields are changed. So if a task is complete or it’s dropdown field is changed, you could have a rule to update any of the formula’s input fields (such as a task’s due date, estimated time or a number type field) so that the formula field would be updated accordingly.

Perhaps if you could explain this as a real world scenario, I could provide a better answer.

Sorry, the error is "Add() expects two input fields’ but i don’t understand this error as I thought you could add up to 10 fields to add together (number fields)

Hi @Jenna_Nelson , welcome to the forum :wave:

I see you have replied to me, regarding my reply to John above, but I’m not sure how your question relates to that.

Are you using the Sum function as per the examples that are shown in the advanced formula editor? i.e. placing your input fields within the brackets, separated by a comma.

Hello and Thank you!

Yes. I am trying to sum 5 different number fields together and it is limiting me to 2. I found a work around by creating multiple custom formulas then summing those together but I was under the assumption I could just create one custom formula with multiple number formulas to get an aggregate.

What I want: ADD (number 1, number 2, number 3, number 4, number 5)

I get an error.

I love the functionality here and how it can calculate the duration based off of due date. However, is there a way we can do the reverse with the default Asana due date field?

I’ve managed to create a dummy with a field for duration, work start date and due date_ which calculates the new due date field if we change the work start date based on duration, but this would be great whether in formula or rule where we can use it to update the default due date field as this is used for so many other rules eg. adding a comment when overdue etc.

Hi @Jenna_Nelson , that’s strange. Could you upload a screenshot perhaps?

I assume your ‘number 1’ etc are number custom fields right?
Have you tried to just use these fields with just a + in between them? i.e. without using the ADD function? That also works, as per the examples you will see in the Advanced editor.

Hi @Kim_Adams , unfortunately formulas cannot influence the value of Asana’s native due date field. But rules sure can, but it depends on the trigger. From your post, I’m not quite clear on what trigger that would be in your case, though.

Rules can now also change the values of number fields so perhaps you could use such a field as an input field in your formula field to influence the result. Just an idea.

And for what it’s worth, there is actually a native ‘duration’ field in the new Gantt view, where whatever number of days you type in, it will adjust the due date for you.

The new formula features are really cool, thank you! I’ve created a formula for profit based on tracked time @ £60/hour. As mentioned above, ‘Time + 0’ gives the time as a number of minutes, which conveniently gives me the total cost to the company in £. If I subtract that from the price we quoted the client, I get the profit in £, which is great.

To go a step further, it would be great to see that as a profitability ratio – but ideally I would want to be able to change the format of the formula result (e.g. to %).


Hi @Ben_Horrigan , nice going there with your formulas!
Yeah, formatting to show % would be great and hopefully something that is coming in the future.
For now, best practice would be to append a % to your Profitability field title so it’s clear that formula’s results are percentages.

1 Like

This topic was automatically closed after 2 days. New replies are no longer allowed.

Hey everyone, I’m excited to share more great news with you; a much-wanted feature is gradually rolling out for Advanced Formulas: the TODAY function! :tada:

With this update, you will now find the new Today () function allowing you to use today’s date in a duration formula calculation for things like how long a task has been open (TODAY() - Created On ) or how long until a task is due (Due Date - TODAY().

Here’s a few examples to get you started; copy & paste the following formulas into the Advanced tab:

Days left formula

Calculate the time between the task’s due date in relation to today.
Result in #d #h format or, if value is greater than a week, in #w #d
[[$DUE_DATE]] - [$TODAY_FUNCTION]

Result in number of days in decimal format
( ( [[$DUE_DATE]] - [$TODAY_FUNCTION] ) + 0 ) / 1440

Days open formula

Calculate the time between the task’s creation date in relation to today.
Result in #d #h format or, if value is greater than a week, in #w #d
[$TODAY_FUNCTION] - [[$CREATED_ON]]

Result in number of days in decimal format
( ( [$TODAY_FUNCTION] - [[$CREATED_ON]] ) + 0 ) / 1440

:bulb: Just a last tip, the Today function will display () right after it. Simply leave these as they are. It does not expect you to insert anything, and even if you do, it will flag it as an error.

How will you use the new TODAY function in your formulas?

1 Like