How to extract date when status changed?
You, as a manager, have probably faced one of these questions:
- How do you get a Status Entrance Date in Jira?
- JQL for Filter by date and status?
- Can the date be extracted on a Status Change by JQL?
- How do you extract a list of dates and times when tickets within a specific project changed?
- How can I write a JQL filter showing the date when an issue last transitioned from one status to another?
- How do you write a JQL to show the date when the issue status changed from "X" TO "Z"?
- How to get dates when an issue transitioned in a status?
- How do I get a report in jira about dates my issue passed by a specific status?
Teams that use Jira to manage their work need to understand when issues arrive in each status. This is important for identifying bottlenecks, improving processes, and accurately forecasting timelines. However, Jira's built-in reporting provides limited visibility into these status transition times. This makes it difficult to pinpoint problem areas and uncover optimization opportunities. I have merged the questions mentioned above into two answers, which I will provide here.
How to show the date when the issue status changed using JQL?
JQL has historical searching for terms like 'Changed' or 'during', it has limitations. For instance, an issue can execute the same transition more than once. Therefore, JQL is unable to search for this data in Jira alone. However, for some cases, JQL works.
To hide certain Jira issues, you can start by finding the JQL that returns the ones you want to hide. For example:
updatedDate<-5d AND status=DONE
Once you have found the JQL, you can negate it to get the desired result. In this case, the negated JQL is:
updatedDate >= -5d OR status != DONE
If the report is for a short period, you can try using JQL like
status CHANGED FROM "X" TO "Z" on 2024-04-22
or
status changed to ("In QA", "IN PROGRESS") AFTER "-1w"
However, if a transition occurred during a specific period, it will not give you the details you are after. In this case, you can use JQL like :
STATUS Changed AFTER '2022-04-22 10:00' BEFORE '2022-04-22 12:00' TO Approved BY username
How to extract a list of date and time when tickets within a certain project changed?
Calculating the time spent in a specific status out-of-the-box in Jira is not possible. However, there are two ways to achieve this.
The first method involves coding that parses the issue history rest API JSON for each issue.
The second method is by using Time in Status. This add-on reports the time spent in each status, status entry dates, and status transition count. Moreover, the app considers your working schedule and calendar, thereby providing accurate results. For instance, if a bug is opened on Friday and closed on Thursday, the time spent in the "In Progress".
But with calendar status will be only four working days, rather than six elapsed days.
In addition to the above, Time in Status provides various other reports, such as assignee time, cycle time, lead time, transition count, resolution time, time to resolve, time to market, and average in-progress time per project. All these reports are available as gadgets on the Jira dashboard, making it easy to track your project's progress.
To extract the date when the status changed, follow these steps:
- Choose the Status Entrance Date report
- Filter issues by assignee, project, sprint, JQL, or any other option you need.
- Select the date ranges for the data calculation.
The Status Entrance Date report gives you granular visibility into your real-world cycle time and workflow patterns, which can expose bottlenecks and inform more accurate forecasting. Take advantage of a 30-day trial to explore the complete suite of features. Additionally, our app is free for up to 10 users.
Have a nice day!
See you in Time in Status 👋