How to calculate total spent spent by a Story in particular Status.

[Jayanth]

I have a query. How can I find the total spent by a Story in particular Status (say, "In Progress"). I want to achieve this using Jira automation only without using any Add-ons or third party tools. 

Note: Here, the story can be reopened. So, the time should be calculated accordingly.

[Bill Sheboy · Accepted solution]

Hi @Jayanth 

Assuming you mean the total time spent in a specific Status...

Short answer: That is not easily possible with automation rules.

Without a marketplace app, there are at least four approaches to gather time spent in a specific Status:

1. use the built-in, Atlassian interpretation of a Control Chart report
2. use an automation rule with custom fields to track when an issue enters a Status and leaves it, calculate the time difference, and add that value to an accumulation custom field
3. use an automation rule which looks at the issue changelog using the REST API endpoints, paging through the entries when necessary
4. use one of the many spreadsheet addins which can iteratively call the REST API endpoints to load Jira data which has paging, and then use formulae to attempt the measurement

The first one may (or may not) accurately track when an issue returns to a Status (such as with backwards flow).  The documentation says it does, others have sometimes reported it does not.

The second approach with an automation rule requires that the automation engine never / rarely has an outage.  This is important because after an outage, some rules may not trigger when the event occurred, and others will not trigger at all.  Both of these reduce the accuracy of measurement.

The third approach uses the issue changelog and automation, and in theory is as accurate as a marketplace app.  The problem is there are limits to the number of changelog entries returned by the REST API, and so repeated calls and paging are required.  The only ways I know to do that and accumulate a total are: with a recursive rule using an Incoming Webhook trigger, or to add a bunch of unique Entity Properties (one for each page checked) and then sum them later with another rule.  Due to the complexity of these approaches it would probably be easier to write your own app outside of Jira to perform the measurement using the REST API.

For the fourth, spreadsheet approach: I have never tried that for time measurement so you may need to do some internet searches to find examples.

Kind regards,
Bill

[Jayanth]

Hi @Bill Sheboy 

Thanks for the information.

[Gizem Gökçe _OBSS_]

Hello @Jayanth ,

I've noticed that you are on free plan in Jira. So if you ever decided to consider 3rd party apps to track how much time each story spend on each status and create detailed reports you can use Timepiece - Time in Status for Jira, developed by my team at OBSS, free of charge.

Timepiece automatically calculates the total time issues spend across their lifecycle or within specific statuses, such as from creation to resolution. Key features include:

• Status Duration Report: Shows time spent in each status and allows combining statuses to calculate metrics like Issue Age, Cycle Time, Lead Time, and Resolution Time.
• Export Options: Generate reports in formats like CSV or XLS.
• Dashboard Gadget: Add reports directly to Jira dashboards for easy access

Let me know if you’d like help setting up this report or need more information! If you wish, you can also schedule a live demo. We will provide a comprehensive overview of the application and address any inquiries you may have.

Hope it helps,

Gizem

[Vitalii_Bobak_SaaSJet]

Hi @Jayanth 

I noticed that you are currently using the free Jira plan and have chosen not to use any add-ons. If your decision is primarily based on cost, I wanted to inform you that our add-on, Time in Status, will be available to your team at no charge.

With Time in Status, the time will be accurately calculated, even if a story is reopened.

If you will be open to add-ons, here’s how to generate a report using Time in Status in just a few steps:

1. Choose Time in Status report by JQL issuetype=story 
2. Filter issues by Issues and Time Ranges
3. Get you report as Grid or Chart view. 

You can also book a live demo - we'll show you the application inside out and answer all your questions.

Add-on also developed by my team and free up to 10 users. 

I hope you find this helpful 🚀

[Hannes Obweger - JXL for Jira]

Hi @Jayanth

I understand that you don't currently want to use a Marketplace apps. If this is primarily a matter of costs, keep in mind that there are a number of relevant apps available that are free of cost for up to 10 users.

If this should be an option for you, you may want to have a look at 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.

Any questions just let me know,

Best,

Hannes