Formula custom field with Advanced Editor

Hi @anon66725527 , 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 @anon66725527 , 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 Input! :tada:

With this update, you will now find the new Today input 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]]

Result in number of days in decimal format
( ( [[$DUE_DATE]] - [[$TODAY]] ) + 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]]-[[$CREATED_ON]]

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

:bulb: You can also find the Today input in the Basic formula editor too:

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

3 Likes

When trying to use ( ( [$TODAY_FUNCTION] - [[$CREATED_ON]] ) + 0 ) / 1440, I’m getting:

Today() expects two input fields

Hi @FreshyJon , can you just check whether you have the Today input available from your Inputs list in the Advanced editor? If not, then the formula will not copy/paste correctly, as shown in your screenshot - it does not appear to recognize it.

The TODAY input is gradually rolling out so it may take a few days to appear in your Asana space.

Looks like I’ve got it now, but is seems “weeks” is the largest designation of time? Is there a way to due a diff between Today and Date, but have the result show as Yrs and Mo? Or even years in decimal? Like 2.5yrs?

Hey @FreshyJon , to get the result in decimal years, it should be possible if you add my above formulas within a new set of ( ) followed by /365.25

For example:
( ( ( [[$DUE_DATE]] - [[$TODAY]] ) + 0 ) / 1440) /365.25

Wow this is exciting!

Is there an ETA on the roll-out? The function isn’t available in my Asana space yet.
Thanks

HI @Grace_Marcon , that’s strange - I was informed by the Asana team that the feature was fully rolled out last week.

Have you tried refreshing your browser or closing and restarting your Desktop app?
Look for it in the Advanced formula editor under Inputs, as per my screenshots above.

I’d suggest that if something is beyond a certain number of weeks (arguably beyond 4 weeks), that you then change to month as the next time unit, and then after 11 months/weeks/days, switch to years/mo/days.

In this particular case, we’re using it to track company anniversary dates compared to today, so these will often be in years. Seeing something as 600 weeks is silly.

Hi @FreshyJon , I’m not sure if you are replying to me or the Asana team (I’m a volunteer forum leader and Solutions Partner, I don’t work for Asana) but since you replied to my post, I think you’re suggestion is great. I recommend you search the Product Feedback category for any existing/similar post, and if not available, then feel free to create a new one with your suggestion so that it can accumulate votes, which the Asana team monitors.

By the way, did my workaround to display the duration in decimal years work for you?

Yes, it worked, though I found
( ( [[$TODAY]] - [[$FIELD]] ) + 0 ) / 525960
to be a little more accurate.

Right… 365.25 * 1,440 = 525,960 (minutes in a year)
Nice!