📆 How you can use the Today input in Advanced Formulas, today!

The Today input is finally here! :tada:

Below are some handy formulas which you can copy/paste into the Advanced Formula editor, available for Asana Business/Advanced tier and above.

Alternatively, you can use this link to import a project, that contains all these formulas, into your Asana space. :star_struck:

:bulb: Hack to use formulas with the Today input in Portfolios, until it is becomes available…
To use the below formulas in a Portfolio, first create the formula field in a project, then add it to your field library and then add that field to your portfolio by searching for it in your library.

1. Time spent from creation date, up until today

Calculate the time between the task’s creation date in relation to today.
You can replace CREATED ON with any date field such as employee’s start date (see #5 below) or birthday (see #6 below) or the native ‘Start date’ input.

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

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

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

2. Time remaining from today, until due date

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

Result in number of months in decimal format
( ( [[$DUE_DATE]] - [[$TODAY]] ) + 0 ) / 43920

Result in number of years in decimal format
( ( [[$DUE_DATE]] - [[$TODAY]] ) + 0 ) / 525960

3. Planned percentage of completion, up until today

Calculate the percentage a task or a project is completed, based on it’s start date & due date, in relation to today.

Result in number with decimal format
100 - ( [[$DUE_DATE]] - [[$TODAY]] ) * 100 / ( [[$DUE_DATE]] - [[$STARTED_AT]] )

:bulb: until formatting the result of formulas is available, it’s a good idea to call this field “% Planned complete”, i.e. include the % at the start/end of the field title.

4. Planned budget spent, up until today

Calculate the budget that should have been spent up until today, assuming that budget is spent linearly between a task or project’s start date and due date.

Formula is as per above formula, but instead of 100, replace it with your ‘Budget’ field.
Result in currency (or just a number with 2 decimals, if using a custom currency for your Budget field)
Budget - ( [[$DUE_DATE]] - [[$TODAY]] ) * Budget / ( [[$DUE_DATE]] - [[$STARTED_AT]] )

5. Employee’s time employed, up until today

Calculate how many years an employee has been at your company. This formula is similar to #1 above but you simply replace CREATED_ON with a date field, let’s call it ‘First day’ (at work).

Result in number of years in decimal format
( ( [[$TODAY]]- First day ) + 0 ) / 525960

:bulb: if you want the result in days or months, follow the same logic as per the other formulas in #1 above.

6. Person’s age, up until today

Calculate a person’s age based on a dedicated date field for their birthday. This formula is similar to #1 above but you simply replace CREATED_ON with a date field, called ‘Birthday’.

Result in number of years in decimal format
( ( [[$TODAY]]- Birthday ) + 0 ) / 525960

:bulb: if you want the result in days or months, follow the same logic as per the other formulas in #1 above.

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

22 Likes

Awesome, thanks @Richard_Sather

“Today” was a good day :wink:

6 Likes

A valuable resource, @Richard_Sather; thank you!!

Larry

2 Likes

Bookmarking this today!

2 Likes

Amazing! Thanks Richard. We’ve been hoping for “Today” based formulas for a long time!

Maybe you could set me straight, but I think our account may have a bug. I copy pasted your “( ( [[$TODAY]] - [[$CREATED_ON]] ) + 0 ) / 1440” formula to show days since creation, and all new tasks start -1 day in the negative. Anybody else running into that? See attached screenshots.

The task I created today shows “-0.68” days since creation (but in the history shows created 54 minutes ago).

Processing: Screenshot 2024-03-21 at 11.14.51 AM.png…

1 Like

Hi @Tanner_Wilson , glad you found my post useful and you are leveraging the new TODAY input!

Yeah the fist day will look a bit weird - I think this happens because the Today input starts counting from the first minute of the day, so it will be a negative until midnight of the following day. It looks like you created the task at 68% of the day, say around 16:00-16:30 your time?

So it should logically go positive as of tomorrow! :wink:

Oh that makes sense! “Today” technically does mean starting at 12:01am midnight right?

That’s probably gonna throw a lot of people off. I wonder if maybe they’ll ever make a “Now” function? I think most people using “Today” just as much mean “Now” and that might be clearer to prevent the 12:01am negative time creation issue.

2 Likes

@Tanner_Wilson , could you confirm the creation time of the task in question? That way we can understand from what time it starts counting the day. I could also run some tests I guess…

I think this is a bit of an edge case because we are also translating to decimal days. If you use the basic editor for the same Today - Created on, then you may find a more sensible result not going negative. Also worth a test!

So we’re using this as a “days since creation” measure. Originally I’d set this up without your formula, just a basic “Date created” - “today” kind of thing. And that was also showing a negative on the first day, probably again because of the 12:01am thing you pointed out.

Might be fringe, but I think anybody that uses the “today” field to track days since creation is going to be confused on day 1 when they first make a task as to why it’s “Days since created” shows “-22 hours” ago. They won’t know “today” is backward referencing 12:01am.

1 Like

Hi Richard, wow, thanks for sharing these useful formulas. I am using the “Employee’s time employed, up until today” formula for a LinkedIn staff anniversary shoutout tracking project. If we wanted to show a whole number rounded down with no decimal point, is that possible?

2 Likes

AMAZING! I was waiting for this TODAY functionality specifically for number 2. Can you divide that result by estimated duration, to essentially create an urgency score of some sort? I would test this myself, but just downgraded to a lower plan this week to see if I can work with the lean set of features.

1 Like

Sure @Bobby_McGivney , I don’t see why not! :+1:

Hi @Sarah_Cummings , glad you found these formulas useful!

Unfortunately, as it stands, it’s not possible to round up/down or format the results of a formula field… :man_shrugging:

1 Like

@Tanner_Wilson , you could try compensating for the discrepancy by adding 1440 (minutes in a day) instead of the 0.

Or whatever number you feel may provide a reasonable result!

Richard, this is a brilliant post. Thanks for outlining all these resourceful formulas here. :clap:t3::clap:t3::clap:t3:

2 Likes

Thank you @Rashad_Issa , that’s very kind of you! Glad you find these useful :+1:

1 Like

@Richard_Sather, #2 is also what interests me most. Tested it and it shows a neat “17 [days remaining]”. If only I could substract week-end days, then I’d have the info that I would really want: xy workdays remaining to get this done.
Is Asana aware of how useful such a countdown feature could be?

1 Like

Hi @Martin_Lewerentz , I agree that would be super useful. Hoping the Asana team will eventually add IF conditions to formulas. :pray:

Btw, you can switch to the Gantt view to simply see the duration of tasks in work days, but unfortunately not from ‘today’.

2 Likes

Hello Im using the field for “days since creation”. One thing though, is that on the tab All activity for every task, it keeps updating the field daily, Asana changed Creation Date from “##” to “##+1” is there any way to stop this daily updating? @Richard_Sather

Hi @Ulises_Solorio , welcome to the forum :wave:

Unfortunately there isn’t a way to stop this - it’s the expected behaviour, for better or for worse.

If the ‘All activity’ tab appears too cluttered, you can always switch to the ‘Comments’ tab instead.

1 Like