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

Ooh that’s a tough one @Arthur_BEGOU, but doable. I have built similar systems but not one that adds earned leave days throughout the year. Conceptually, I would approach this as per @lpb good tip here whereas a “Leave days” number field has positive values for ‘earned days’ while the ‘leave days taken’ would be negative values, thus getting a sum of how many days are left.

The negative leave days would be generated by a leave submission form, which could be processed within a ‘back-end’ project before any ‘approved’ leave days are then added to a public ‘approved leave days’ project for all to see. I use Flowsana.net to invert the positive values (inserted from the form) to negative values - otherwise, the HR member/reviewer can insert these manually, based on the requester’s leave start/end dates.

In a separate ‘back-end’ project (managed by HR), I would set up everyone’s ‘earned leave days’ as one task for each person which recurs every 2 months with a +2 days number value. With a rule that when dude date is today → complete task, then upon completion, it would create a new task for another +2 days. All these tasks would be multi-homed to a comment-only member-facing project, filtered by dynamic ‘Me’ so they can only see their leave days left. Alternatively, if you have a small team, you could set up one private project per member, which could all roll up into a Portfolio so that HR/MGMT can also see each person’s leave days left.

I think you get the gist, all this can run using rules, and, as you can see, without any formulas included - they will not do this for us just yet. I am planning on creating a video series for everything around leave days
 hopefully I’ll get around to that soon, maybe before this is all natively possible within Asana! :sweat_smile:

2 Likes

Whaaaaa I’m extremely impressed. :star_struck:
I will try to follow that guide to create something.

1 Like

Give it a spin and let me know how you do! :muscle:

1 Like

Any tips on how I could convert the variance field to hours and minutes rather than days?
Screenshot 2023-12-14 at 12.08.00 PM

Hi @Liv_Tillema , you won’t be able to get it into hour and minutes, but you can get it into hours with decimals if you use the below formula:

( ( Estimated time - Actual time ) + 0 ) / 60

So, for example, 1 hour and 30 minutes would display as 1.50.

Here’s your example, converted using this formula (which will also work better for your dashboard charts):

3 Likes

Hi @Richard_Sather !

Thank you very much for the information; it’s truly helpful!!

Do you know if it’s possible to calculate the number of days excluding weekends? I’m trying to create a task to track the number of vacation days, but it includes Saturdays and Sundays, and I would prefer not to include them in the calculation.

1 Like

Hi @Thiago_da_Silva , glad you found this useful! :slight_smile:

Great question! Unfortunately, there isn’t a native formula in Asana that can do this. You could subtract 2 days for each week to account for a weekend but that may still not be accurate if a leave date is less than a week, you can’t tell if it will cross over a weekend or not.

Therefore, I tend to use a simple number field to be filled out by the form submitter, and then checked by a human, i.e. perhaps the HR manager who checks that all data within a form submission have been correctly filled out, before ‘forwarding’ for approval(s).

Perhaps others have better ideas?

@Richard_Sather Thank you for all your help with peoples questions


I am trying to figure out how to keep a running tally and reduce it each task


e.g. I have a bucket of 10,000 units in a project, and I use a task to capture usage of the units, say Task#1 uses 400units, Task#2 uses 300 ect
 is there a way to show remaining units either in each task or for the project? I don’t see anywhere to add in the Sum of a custom field in the advanced formula.

Thanks!

Hi @Darren_Tuck , welcome to the forum :wave:

Thank you for your nice comment. :slight_smile:
I’m not sure that a formula field is what you need in this case. I think you could create your tasks as per below, while using a simple number field for your units:

Task name Units (number field)
Task 1 10,000
Task 2 -300
Task 3 -400
SUM 9,300

If you use positive and negative numbers you could get your tally from the SUM function at the bottom of your list. And you could also create charts in your project’s Dashboard.

Here is a useful guide that you could also refer to:

Let me know if I missed something!

1 Like

This works as a sum but I am trying to see the usage per task

So Task1 used 400 units, 9600 remain (shown in a column) Task2 used 300 units, 9300 units remain

Oh right, I see!

So you would need an advanced formula field called ‘Units Remaining’ to be as such:
10,000 - ‘Units used to date’ field

That is assuming that you always start with 10,000 units for all those tasks in this particular project.
You could use this field as a local formula field per project and simply edit the 10,000 to another number, per project. i.e. don’t add into your library, unless you call it specifically, for example ‘Units remaining from 10,000’.

Does that work for you?

Yes thank you, seems a formula per project is the way to go for that.

Was hoping there was a way to auto calculate the “Units used to date” (running tally including that tasks units) so that the entry would be only need to be units, but without being able to reference the declining balance from the previous task I cant see a way to do that.

Hey everyone, I’ve put together a list of formulas that use the new TODAY input that you can easily copy/paste into the Advanced formula editor, to use in your projects:

3 Likes

Hi @Richard_Sather , do you know if it is possible to remove the decimals of the result of an advanced formula?

I have a client that has a formular that converts delays into Days but they want a value with no decimals.

Thank you in advance

Hi @David_Jane , we can’t natively control or define the format of a Formula field’s output, yet. However, if a number field is formatted without any decimals, then when that field is used as an input, along with certain functions, the output will remain without decimals, also.

Thank you @Richard_Sather :pray:t2:

1 Like

Hi @Richard_Sather - do you have any tips on how to leverage formulas to extract capacity information? thanks!

Hi @Scott_Levine1 , could you add a bit more context to your question? Does the Workload feature within Portfolios not work for you?

You could leverage a formula field for the Workload’s effort level, as long as that formula exists in the projects within your Portfolio.

thanks, @Richard_Sather !

The best I can tell, Workload offers “metrics” to show different types of impact on a person (e.g. task count, estimated time, and other custom number fields). I am interested in a metric that calculates the % of that persons capacity that is remaining (or being used) - which, as I understand it, is NOT what Percent allocation does - it’s another input value.

As far as I can tell, Workload in a Project or Portfolio work and display information the same way - but would be grateful if i’m not seeing something!

thanks for considering!