Time in status and cycle time from Jira, in Google Sheets
Jira shows when an issue changed status, one issue at a time, in its history. It has no column for “days in In Review”, and no report of the cycle time per issue. Teams ask for this often: the two requests for it on Atlassian’s own tracker have about 240 and 190 votes.
The app works these out from each issue’s status history and keeps them in your spreadsheet.
Columns for the report tab
Section titled “Columns for the report tab”In the report’s settings in Jira (More options → Columns), add any of these:
| Column | What it shows |
|---|---|
| In current status since | When the issue moved into its current status (or was created, if it never moved) |
| Started (UTC) | The first time the issue moved into an In Progress status |
| Cycle time (days) | From Started to the latest move into a Done status. Empty until the issue is done |
| Times reopened | How often the issue moved from a Done status back to one that isn’t done |
“In Progress” and “Done” mean Jira’s status categories (the colours of the status lozenges), so these columns work with any workflow and any status names.
A tab with days in each status
Section titled “A tab with days in each status”Turn on Time in status under More options. The app adds a tab named after the report, “… time in status”: one row per issue, one column per status, in the order work flows (to do, in progress, done), and a total.
- Days are calendar days, to one decimal. The current status counts up to the time of the update.
- The tab is rewritten at each full check of the filter (daily; weekly for very large reports). Keep your notes in the report tab, not here.
- An issue that went through a status twice gets the days of both visits added together.
What it costs
Section titled “What it costs”Nothing extra in Jira’s request allowance: the status history comes with the same search that reads the issues. For an issue with a long history, the app completes it with one more request for many issues at once.
Chart it
Section titled “Chart it”The tab’s headers are in row 2. To chart the average days per status, select the data from row 2 down and choose Insert → Chart; see Chart the report for Looker Studio and pivot tables.