đŸ§Ș 12 Formulas to get you started with the new Advanced Editor

English Forum Tips and Tricks Forum Leaders tips
1

Hi everyone! I hope you are all excited about the recent roll out of the Advanced Editor for Formula fields as I recently announced here.

That post includes a video where I had the chance to showcase various use cases, so I thought I’d create this post to share more details on how I built them, which will hopefully inspire you while you try out the Advanced Editor for Formula fields (rolling out on Business and Enterprise tiers).

1. Task Duration:

:white_check_mark: Prerequisites: Start & Due dates on tasks
:test_tube: Formula 1: ((Due date-Start date)/1440)+1 = Duration a task will take in days, or copy paste this into the adv. editor: (( [[$DUE_DATE]] - [[$STARTED_AT]] )/1440)+1
:test_tube: Formula 2: ((Due date-Start date)+0)/60 = Duration a task will take in hours with decimals, or copy paste this into the adv. editor: ( ( [[$DUE_DATE]] - [[$STARTED_AT]] ) + 0 ) / 60
:test_tube: Formula 3: ((Due date-Start date)/1440)+1*24= Duration a task will take in hours without decimals, or copy paste this into the adv. editor: ( ( [[$DUE_DATE]] - [[$STARTED_AT]] ) / 1440 + 0 ) * 24
:memo: Note: The exception is that these formulas do not display a value of 1 day for tasks that only have a due date. The hack is to simply remember to insert the start date as well, to be on the same day as the due date! Also, you can toggle between using +0 or +1 which will either add a day if you want a task on a single day to count for one day long.

2. Project Duration in days (for Portfolios)

:white_check_mark: Prerequisites: your projects have a start and an end date, i.e. the ‘Date’ field in your portfolio is populated accordingly.
:test_tube: Formula: 0+(Date-Start date)/1440 = Duration of project in days
or copy paste this into the adv. editor: 0 + ( [[$DATE]] - [[$STARTED_AT]] ) / 1440
:memo: Note: in Portfolios, the Due date is referred to as simply ‘Date’.

:bulb: To show the value in weeks AND days (better than the native ‘Duration’ field in Portfolios:
:test_tube: Basic Formula: Date - Start Date

3. Project Daily/Weekly Budget (for Portfolios)

:white_check_mark: Prerequisites: your projects have a start and an end date, i.e. the ‘Date’ field in your portfolio is populated accordingly. And you also have an ‘Estimated budget’ currency type number field.
To show the Daily Budget:
:test_tube: Formula: Estimated budget / ((Date-Start date)/1440)
To show the Weekly Budget:
:test_tube: Formula: Estimated budget / ((Date-Start date)/1440) * 7
(where 7 is the days in the week, but this could also be 5, depending on the working days)

4. Remaining Project Budget (for Portfolios)

:white_check_mark: Prerequisites: your projects have an ‘Estimated budget’ currency type number field as well as an ‘Actual spent’ currency type number field.
:test_tube: Basic Formula: Estimated budget - Actual spent

5. Set the Estimated budget based on the Daily Budget (for Portfolios)

:white_check_mark: Prerequisites: your projects have a start and an end date, i.e. the ‘Date’ field in your portfolio is populated accordingly. And you also have a ‘Daily budget’ currency type number field.
:test_tube: Formula: Daily budget * (Date-Start date)/1440
:memo: Note: add 2 decimal points to ‘Daily budget’ currency field to get the resulting ‘Estimated budget’ currency value in 2 decimals also.

6. Story Points to Estimated hours:

:white_check_mark: Prerequisites: You have a number type custom field called ‘Story Points’:
:test_tube: Formula: Story Points * 8 = Estimated hours
(where 8 is the amount of hours that is equal to 1 story point, i.e. in this case, one working day)
:memo: Note: add 2 decimal points to your ‘Story points’ number field to get the ‘Estimated hours’ formula in 2 decimals also.

7. Story Points to Hours remaining:

:white_check_mark: Prerequisites: You have a number type custom field called ‘Story Points’ and using Asana’s native ‘Actual time’ field.
:test_tube: Formula: (Story Points * 8) - Actual time / 60 = Hours remaining
(where 8 is the amount of hours that is equal to 1 story point, i.e. in this case, one working day)
:memo: Note: add 2 decimal points to your ‘Story points’ number field to get the ‘Estimated hours’ formula in 2 decimals also.

8. Hourly rate * Actual Time = Cost

:white_check_mark: Prerequisites: You have a currency type number field called ‘Hourly rate’ and using Asana’s native ‘Actual time’ field.
:test_tube: Formula: Hourly rate * Actual time / 60 = Cost
:memo: Note: you do NOT need to add 2 decimal points to your ‘Hourly rate’ number field since the resulting currency will be in 2 decimals.

9. Hourly rate * Actual Time = Cost + TAX = Total Cost

:white_check_mark: Prerequisites: As per above formula.
:test_tube: Formula: (Hourly rate * Actual time) * 1.24 = Total Cost including Tax
(where 1.24 = 24% tax)
:memo: Note: you do NOT need to add 2 decimal points to your ‘Hourly rate’ number field since the resulting currency will be in 2 decimals.

10. Show the value of added TAX to any cost value

:white_check_mark: Prerequisites: Any formula which includes a currency type number field.
:test_tube: Formula (example): (Hourly rate * Actual time) * 0.24 = Total value of Tax
(where 0.24 = 24% tax)
:memo: Note: you do NOT need to add 2 decimal points to your ‘Hourly rate’ number field since the resulting currency will be in 2 decimals.

11. Cost per item x Quantity with applied discount +TAX

:white_check_mark: Prerequisites: Any formula which includes a currency type number field. eg. A ‘Cost per item’ currency type number field and a number field for ‘Quantity’ (either one of these fields should have 2 decimals)
:test_tube: Formula (example): (Cost per item * Quantity) * 0.9 = Total value with a 10% discount
(where as 0.9 = -10% discount, 0.5 would be a 50% discount, 0.1 would be a 90% discount)
:memo: Note: add 2 decimal points to ‘Cost per item’ currency field to get the resulting formula’s currency value in 2 decimals also.

To show the value of TAX added after the discount:
:test_tube: Formula (example): (Cost per item * Quantity) * 0.9 *0.24
(where as 0.24 = 24% tax)

To show the value including TAX after discount:
:test_tube: Formula (example): (Cost per item * Quantity) * 0.9 *1.24
(where as 1.24 = including 24% tax)

12. Cost per coverage x Area with applied discount +TAX

:white_check_mark: Prerequisites: you have a number field for ‘Area’ and a currency type number field for ‘Cost per coverage’, i.e. in sqft / m2 (either one of these fields should have 2 decimals)
:test_tube: Formula (example): as per above formulas but instead of ‘Quantity’ use a number field for ‘Area’ and instead of ‘Cost per item’, use ‘Cost per coverage’.
:memo: Note: you will need to remove any custom labels for your ‘Area’ field, such as ‘sqft’ or ‘m2’ appended on the end as a custom label. Edit this field to change it to a simple number so that the resulting formulas’ values will show as per the currency set by the ‘Cost per coverage’ field (sqft / m2).

:rocket: See all of my top tips & tricks on my website.

25 Likes
3

One more great post from you! Well said @Richard_Sather :clap:

Thanks for sharing your expertise with the rest of us :pray::sparkles:

2 Likes
4

Richard is on fire! cc @Julien_RENAUD @Arthur_BEGOU

3 Likes
5

WOW :star_struck: Thanks for sharing @Richard_Sather, this is such a valuable and handy post for customers working with Formulas!

1 Like
6

Bookmarking this one

2 Likes
7

Awesome, thanks @Richard_Sather :+1:

1 Like
8

Bravo!!!

1 Like
9

Fantastic :rocket:

1 Like
10

Great post @Richard_Sather !!!

11

Thank you all for your kind words and support :heart:

1 Like
12

This is great! When will this go live?

13

Hi @Thorben_Scheidegger , welcome to the forum :wave:

It is currently gradually rolling out to all Business and Enterprise customers!

Look out for the ‘Switch to advanced mode’ button in the Formula custom field editor and make sure to refresh your browser daily (or close and reopen the desktop app) to make sure you get the update!

image
image1086×1160 56 KB

14

Just to clarify, that’s all Business and Enterprise customers. :slight_smile:

15

Good point @Phil_Seeman , I just amended my post! Thanks

1 Like
16

It is very exciting! But Asana, don’t say “released on August 4th” if this means “we will start on August 4th and don’t when we’ll finish” ^^

1 Like
17

Why the “0+” ?

:scream: story points should never be converted to time :sweat_smile:
(just trolling)

Is this a weird Greek thing? ^^ in France we have 20%

Thanks for the post!

1 Like
18

Hi Richard, I am having business license but I am not yet able to see " switch to advanced mode". Is there anything more we need to do to access the same? Thanks for ur effort

19

Hey @Bastien_Siebman , adding a +0 (or any number) after a start/due date or a native time field (Estimate or Actual time) will convert it into minutes, strangely enough!

So then dividing by 60, for example, will produce the value in hours but in a number format with decimals. i.e. 1h30m will instead display as 1.5

image
image2252×568 45 KB

Or in my case above, dividing by 1440 (minutes in a day) will display the value in days, again in a number format.

This ‘conversion’ also helps with displaying charts in a dashboard in the aforementioned values, rather than the current native display which is in weeks/days for time, instead of hours per say.
I’ve elaborated on this workaround here:

Oh and 24% tax is a freelancer’s reality in Greece! :exploding_head:

3 Likes
20

Hi @Adamya , apart from this



there is not much more you can do apart from waiting patiently, I’m afraid! :person_shrugging:

Perhaps @Emily_Roman could kindly provide us with an update on the extent of the roll out?

21

Hi @Richard_Sather and @Adamya! Formula custom field with Advanced Editor was launched to all Business and Enterprise customers yesterday :tada:

3 Likes