Need help creating automation to track time spent in status

[Khai Ha]

I'm new to automations and need help creating an automation tracking how long issues have spent in the status In Review. Ideally when something is marked Done the automation would trigger.

I have tried:

Using log work

Creating a time stamp in a custom field

Creating a comment on the issue that has the value

Going in circles, if anyone could help that would be wonderful.

[Petru Simion _Simitech Ltd__ · Accepted solution]

Hi @Khai Ha ,

To track time spent in a status, particularly when an issue is moved in and out of that status multiple times, you can create custom fields to record this data. However, this solution has a significant caveat: it will only work from the moment it's implemented, leaving existing issues without calculated data.

To calculate time spent for existing issues, you'll need to extract data from the ticket history. Interestingly, you can apply the same extraction logic to new issues, eliminating the need for custom fields. While an automation solution is possible, it will require substantial effort for creation and testing.

Another significant challenge arises when trying to analyze and interpret your data as it grows. Storing time spent in custom fields within individual issues can become cumbersome, making it difficult to analyze and export for further examination.

If you are open to using apps, you can use Time in Status Dashboard for Jira  an app released by our company. This will work for your existing issues as well as for issues that will be created in the future.

You can search for issues across an entire project or filter them using multiple fields, please see below in the top filter area.

The results can be displayed in customizable perspectives, allowing you to view each status and time spent in columns or rows. For further analysis, you can export the data to CSV, enabling you to create charts and reports in Excel or other systems.

The flexible perspective options let you choose the view that best suits your needs. Additionally, you can track and export the number of times an issue has transitioned into a specific status, providing valuable insights into your workflow.

You can select from various time formats to display and export the time spent in each status, tailoring the output to your specific needs.

Here are are some samples:

Result in columns

Same result in rows

For instance, in the rows perspective, you can sort by the Status column to easily view the total time each issue has spent in each specific status, at a glance.

Regards

Petru

[Trudy Claspill]

Hello @Khai Ha 

Are you only interested in the time the issue spends in the one status - In Review?

If the issue is in that status, transitions to another status, and then transitions back to In Review, would you want the total of all time spent in that status?

What do you want to do with the information once you have it?

Would you want the information manipulated to align with your Jira configuration for working days and working hours so that, for instance, time in that status during non-working hours and days is not counted?

How would you expect the information to be presented? In raw minutes, or raw hours, or raw days? Or would you want it converted to weeks/days/hours/minutes?

You have to make note of when the issue is transitioned to the In Review state, at a minimum. You could record that in a custom date/time field. You could use a rule triggered by Issue Transitioned, setting the trigger with a destination status of In Review, and then an Edit issue action to set your custom date/time field to now().

https://support.atlassian.com/cloud-automation/docs/jira-automation-triggers/#Issue-transitioned

You could have a second rule that is also triggered when the status changes, with In Review as the initial status of the transition.

You could then do a date/time calculation using the diff function

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-difference---

i.e.

{{now.diff(issue.YourCustomTimestampField).weeks}}

If the issue can be in the In Review status multiple times, that is a more complicated rule. You have to decide if you to accumulate all the time, or if you only care about the first time the issue is in that status, or if you want to make multiple separate recordings of the duration for each time it is in that status.

[Hannes Obweger - JXL for Jira]

Hi @Khai Ha

I trust that you'll be able to resolve this using Jira Automation based on the great answers provided!

Just for future reference, in case it is interesting: If you're open to solutions from the Atlassian Marketplace, there's a number of apps available that can help with this. E.g., your use case would be easy to solve using the app that my team and I are working on: JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the time in [status], time between [status] and [status], and many, many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. Of course, you can also export your data to Excel or CSV in just two clicks.

This all just works - there's no scripting or automation whatsoever required.

Any questions just let me know,

Best,

Hannes

[Ayça Erdem_OBSS]

Hello @Khai Ha 

To track how long issues have spent in the In Review status , I’d recommend considering Timepiece - Time in Status for Jira, developed by our team at OBSS. While your automation idea is great, Timepiece can serve as an essential input source for your automation by accurately tracking and reporting the time spent in each status.

With Timepiece, you can effortlessly obtain precise data on how long an issue has spent in In Review, without needing to calculate it yourself. This is particularly valuable when an issue transitions into the same status multiple times, as Timepiece handles these transitions and aggregates the time accurately—something that is extremely complex and error-prone to do manually.

Additionally, Timepiece provides an API that allows you to pull this data directly into Jira Automation. This means you can use the calculated time spent in a status as an input to trigger automation rules.

We also have detailed Jira Automation use case examples that can help you set up automation rules effectively.

For developers, Timepiece offers comprehensive API documentation to integrate our app's data seamlessly into your workflows.

If you'd like to explore this solution further, feel free to check out Timepiece on the Atlassian Marketplace or schedule a demo with us for a walkthrough of the app's features.

Hope it helps

Ayça

[Valerie Knapp]

Hi @Khai Ha , thanks for your post. 

To do this, you'll need to create a few custom fields. 

First, you need to track when the issue transitions to In review. 

So the trigger is the transition to In review then you need an action to update the field transition date in review with the value {{now}}

Then, you need another trigger for the transition from in review to done to populate another custom field of Done transition date to {{now}} and then you need to do a maths operation to compare the two values to get the amount of time in the status. 

Please check out how to use smart values and maths operations here https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/ 

Please have a go and share with us your progress if you have other questions. 

Best wishes

[Madhu_RVS]

Hi @Khai Ha 

For detailed data and flexibility in reporting, if you would be interested in a mktplace app, you may want to try out our add-on.

Time in Status Reports 

The app has more than 20+ reports to meet a variety of use cases. Time in Status is one of the many reports which the app provides. Do try it out

More details here

Disclaimer : I work for the vendor who built this app

[Khai Ha]

Thanks everyone! I'll take this back to my admin.