Structure formulas for 'spent time in status' column

[Hajnalka Farkas]

Hello, I would like to create columns where I can show how much time was logged on an issue in different statuses (I would made as many columns as many statuses I would like to display) Is it possible to use formulas for this? I only could set columns which shows the time the issue spent in the statuses. I can't figure it out how should I set the formulas. Can you help me?

Thank you

[Mariana_Pryshliak_Saasjet]

Hello @Hajnalka Farkas 

You can try Time in Status for Jira Cloud add-on, which shows how much time an issue has been spending in each status.  There are different columns for each status. 

You don't need any formulas or long configs. In a few clicks, you'll get the necessary data!

Also, you'll get data both in Graphs and Chart view.

I hope you find this helpful.

Best regards, Mariana.

[Hajnalka Farkas]

Hi @Mariana_Pryshliak_Saasjet

Thank you for the reply. However we wanted to solve it within the structure plugin, but we will look into the plugin.

KR, Hajnalka

[Alessandro C_ _ALM Works_]

Hello @Hajnalka Farkas , Alessandro from ALM Works here.

The easiest way to sum the Duration of an Item, in one or more status using Structure, is through the "Time in Status..." column.

You can select Duration from one or a Sum of different Statuses.

This can also be achieved by using the "Formula..." option when adding new Columns as you mentioned. You can then set a "timeInStatus" variable in the formula field, save the formula and then update its definitions by:

1. Click the "! timeInStatus" below the Formula field;
2. Select "Time in Status" on the attributes DropDown (when you first select the drop-down, it works as a search field as well, so you can start Typing to make it easier to find the attribute);
3. Once 'Time in Status' is selected, click on the Cog icon to the right of the attribute field to select which Status you would like to sum as a total.
   I.E.: You can select just "In Progress" or "In Progress" and "Done" to see the total time across both statuses.
4. Change the Format field to "Duration" to make Structure calculate the formula as total of hours tracked.
5. You can then customize other properties as you like, such as Sum a total of Hours based on subitems, if you want to show the total hours worked in all Stories under an epic or select "Work Time" in case your project hours are tracked according to Jira's "work hours per day" settings.
6. Once done, just click outside the Column configuration panel and you should be already seeing the Duration of the selected status.

Please let me know if this helps! I look forward to hearing from you.

Warmest regards,
Alessandro C.
ALM Works.

[Hajnalka Farkas]

Hello @Alessandro C_ _ALM Works_

Thank you for the reply. We will look and test to see if that’s what we’re looking for.

KR, Hajnalka

[Manali Butey]

Hi @Alessandro C_ _ALM Works_ -  I am looking to add a single column to my Structure board which should tell me the time in the status the issue is currently in. How can I achieve this?

Thanks

[Daria_Baltiyskaya_ALM_Works_]

Hello @Manali Butey If you are on Jira Server/Data Center, you can see the time spent in any status using a special Time In Status Column . Just add this column, select the status you're interested in and Structure will show the time spent in this status.

Best regards,
Daria

[Manali Butey]

Hi @Daria_Baltiyskaya_ALM_Works_ - I have 10 statuses, and at a given point in time the issue is in one of the theses status. Instead of making a column each for the time in status I would like one column only that gives me the time in status for the status the issue is currently in instead of giving me a  Σ time in all status the issue has been in. 

Happy to clarify further if required.
Thanks.

[David Niro]

Hello @Manali Butey ,

David from ALM Works here.

There is a way that we can create a column that shows Time in Current Status. This can be accomplished through the use of a Formula Column.

There is some time commitment in getting it set up though. We have to create a Time in Status Column for each of the statuses used in the workflow (don't worry, we will remove these later :)).  From there, we will use these Columns as Variables in our Formula.

Step 1:
Create a Time in Status column for each of the statuses the issues could potentially be in.

You will want to name each of these something unique (this will help later when we map it as a variable)
 

Step 2:
Create the Formula Column

• Use the formula template: IF(Condition1; Result1; Condition2; Result2; ...; DefaultOpt)

  If(status="open";timeinopen;status="reopened";timeinreoopen;status="to do";timeintodo;status="closed";timeinclosed)

• Map each Result to the corresponding column as its variable assignment
• Set the Format from General (default) to Duration

Step 3:
House Keeping

• Because all of your Time in Status Columns are now Variables in your Formula Column, you can safely remove them from your structure
• Save your view

Please let me know if this helps!

Best Regards,
David Niro
www.almworks.com

[Marta Elicegui]

Hello @David Niro

Do you have any suggestions about how i could approach the formula above with the structure plugin in Jira Cloud. 

Thanks!

[David Niro]

Hello @Marta Elicegui ,

On Cloud, the Time in Status column has an option for "Current Status".   You just need to toggle it on when adding the column.

Best,
David

[Alan Dicks Mauri]

Hi @Alessandro C_ _ALM Works_ . interesting response, thanks! QQ... When you use the Time in Status from inside a Formula, is there a way to get the result of the standard work calendar 8/5 (instead of the Standard Calendar 24/7), or is that only possible when not using a formula, and directly using "Time in Status" type for a column? Thanks!

[Stepan Kholodov _Tempo_]

Hello @Alan Dicks Mauri 

There is no Calendar selection when time in Status is references as a variable in a formula. But in this case, the value itself should be the same as the value in the Time in Status column, i.e. if you select the required calendar in the column, the value will be what you need in the formula too. But if you change the Calendar in the column, you might need to remap the Time in Status variable in the formula again for the formula to pick up the update.

Best regards,
Stepan
Tempo (the Structure app vendor)

[Alan Dicks Mauri]

Hi @Stepan Kholodov _Tempo_ . Thanks for the info but I'm not following. My doubt is: How can I use Time in Status variable from inside a formula, that returns the # of days, according to the work calendar 8/5? Thanks!

[Stepan Kholodov _Tempo_]

@Alan Dicks Mauri 
Add the Time in Status column to the structure, set the calendar in its settings as 8/5 and then reference the column in the Formula, mapping the timeinstatus variable to it. I.e. you can't define a standalone variable in the formula with the right calendar, but you can reuse existing value from the Time in Status column (where the right calendar is applied).

If you need further assistance, please reach out to us at our support portal.

Best regards,
Stepan

[Aykhan Mustafayev]

Hi, our Jira board doesn't display the structure data correctly. So, our company's processes run on Jira, and there are many squads here, each with its own space. There are a few problems here. For example, I want to select and view different statuses from the time in status section, but not all the statuses within the project are here. Jira gives us many default statuses here. Although some project statuses are here, they either do not show any information or show errors. How can we solve this problem?

[Stepan Kholodov _Tempo_]

Hello @Aykhan Mustafayev 

To avoid discussing matters that are not about this post's subject - working with Statuses with the Formula column - please reach out to us directly at our support portal with the described issues and we'll get back to you shortly.

Best regards,
Stepan
Tempo (the Structure app vendor)

[Rahul_RVS_Support]

Hi @Hajnalka Farkas 

We have built an addon specifically for this need, as there is no easy way to extract this information out of the box.

You can try Issue Tracker for Jira 

It will provide you below charts on the issue screen itself.

Thanks

[Earl McCutcheon]

Hello @Rahul_RVS_Support ,

Thanks for posting the information, great to see the alternative option, and I have a quick ask for you for a follow-up.

Please review the partner guidelines here, and make sure to update your username according to your relationship as a vendor of the tool, noting:

Identify yourself as a partner: Make sure the user is aware you are affiliated with a partner company; for example, by placing the name of your company in your user name in {curly brackets} or -dashes- (note that regular brackets no longer work and have been automatically converted to dashes). If you are mentioning your own products or services, make sure you include wording to make it clear that you are affiliated with the company providing those products or services.

Regards,
Earl

[Rahul_RVS_Support]

Thanks @Earl McCutcheon for notifying me. I have updated my username accordingly

[Hajnalka Farkas]

Hello @Rahul_RVS_Support 

Thank you for the answear. However we wanted to solve it within the structure plugin

kr:Hajnalka

[Marina Todorovska]

Hi all, at this moment the time in status is updated every minute. How can I make the calculation execute only once a day? Thank you!

[Stepan Kholodov _Tempo_]

Hello @Marina Todorovska 

It's not possible - the refresh rate can't be configured; Structure updates the data in real-time as soon as it can.

If you have other questions - please reach out to us directly at our support portal.

Best regards,
Stepan
Tempo (the Structure app vendor)

[Marina Todorovska]

Hi @Stepan Kholodov _Tempo_ 

thank you for your answer. Any idea on what condition I can put in for it to be executed once? 

Something like 

IF (already executed in the past 24 hours) skip;

ELSE TimeinStatus