Extract date when story changes from New to InProgress using JQL

[Lori Benowitz]

Hello,

I need to generate a report to capture the start and end date for each story in my sprint.  Looking a the delivered reports, I have not been able to find anything that provides this info.  I was trying to build a jql statement that could pull the information.  I have identified the Resolved field to capture the end data, but am not able to identify how to capture when the story moves from the New status to In Progress.

How can I accomplish this?  

[Valeriia_Havrylenko_SaaSJet]

Hi @Lori Benowitz   👋

If you consider installing third-party add-ons, option for you can be  Time Metrics Tracker. The add-on is specifically designed to create reports when the issue moves from the one status to another. 

You can set up highlights for values exceeding the permissible level and receive issue and email notifications in this case.

Also you can book a demo with our specialist to see add-on in action!

If you are interested in report that shows time in each  status option can be Time in Status. To get report like this just 

1. Choose Project, Assignee, Sprint etc.
2. Filter issues by Time Ranges
3. Enjoy you report as Table 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.

I hope you find this helpful 🚀

[Gizem Gökçe _OBSS_]

Hello @Lori Benowitz ,

Welcome to the community!

The required data is available in each issue's history but you will need to use a marketplace app to get ready-to-go reports. Good news that report is available in our application where you can directly learn the transition dates without having to deal with Jira issue histories. The app is Timepiece - Time in Status for Jira , the oldest and the most comprehensive Time in Status app in Atlassian Marketplace, which is built by my team at OBSS. It is available for both Jira Cloud, and Data Center. 

You can use the date-based reports to see the transition dates from/to a status. In the screenshot below you can see the transition dates of the issues to each status. I think this report will be spot on for your needs.

Visit Timepiece - Time in Status for Jira to explore full range of features. 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

[Madhu_RVS]

Hi @Lori Benowitz 

Welcome to the community !!

You can use Jira Automation to populate a custom field on status change date and time. or you can also use Jira Rest API's to pull this data from issue change history.

Another option could be to use a mktplace app. If you would be interested in the same, you can try out our app 

Time in Status Reports 

The below report will suffice the requirement. Also with this app you generate time in status for multiple issues with multiple filter and grouping options. You can also group your statuses to define your resolution times. The app can easily be added as a dashboard gadget.

More details here.

Disclaimer : I work for the vendor who built this app

[Hannes Obweger - JXL for Jira]

Hi @Lori Benowitz 

welcome to the community!

Status transition dates are captured as part an issue's history (you can see it if you click on the history tab in the issue view), but aren't available via JQL. 

What some people do is to use Jira Automation to trigger a rule whenever an issue transitions from one status to the other, and write the date/time into a custom field. This custom field can then be used in JQL, and/or be added to your issue search result.

Alternatively, if you're open to solutions from the Atlassian Marketplace, there's a number of apps available that can help with this. I'll provide more information below.

Hope this helps,

Best,

Hannes

[Hannes Obweger - JXL for Jira]

... and to expand on my last point: If you're open to solutions from the Atlassian Marketplace, this would be easy to do 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 number of so-called history columns that aren’t natively available, including an issue's date of transition from [status] to [status], time in [status], or time between [status] and [status].

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 XSLX (Excel, Google Sheets) or CSV in just two clicks. 

Any questions just let me know,

Best,

Hannes

[Aron Gombas _Midori_]

An easy solution to the problem:

1. Go to the Issue Navigator
2. Run the JQL search that collects your issues, something like: type=Story and sprint=Foobar
3. Add the fields you need in the report to the Issue Navigator column, don't forget "Resolved"!
4. Export the issues using the "Issues with selected fields and repeating change history" export type provided by the Better Excel Exporter app (sample exports)

Now in Excel you can easily filter the changes to those where:

• the change field is "status"
• the old value is "To do" (for example)
• the new value is "In progress" (for example)

The exact time of the change will be there in the next cell. (Don't worry that on my screenshot it is rounded to a date, even hour and minute are available and can be shown by changing the cell format!)

Now you have everything in the spreadsheet!

[Morgan DUBUISSON]

Hello,

To capture the start and end dates for each story in your sprint, you can use a JQL query that tracks the status transitions. Since you've already identified the **Resolved** field to capture the end date, you can also use JQL to capture when a story moves from "New" to "In Progress."

Here's an example query that could help:

sprint = [ID_OF_YOUR_SPRINT] AND status changed FROM "New" TO "In Progress" AFTER "2024/09/01" BEFORE "2024/09/15"

This will return all stories that transitioned from **New** to **In Progress** within the specified date range of your sprint. You can replace the dates with the actual start and end dates of your sprint or use a sprint ID.

If you also want to filter by stories that have been resolved, you can use a separate query for the resolution date:

sprint = [ID_OF_YOUR_SPRINT] AND resolutiondate >= "2024/09/01" AND resolutiondate <= "2024/09/15"This should give you both the start and end information you're looking for. 

Let me know if you need further details!

[Morgan DUBUISSON]

Hello,

I apologize for my previous message — upon reviewing your request, I realized that the data you're looking for (the moment when a story moves from "New" to "In Progress") is not easily retrievable with JQL alone. This information is stored in the issue history, which means JQL isn't the ideal solution.

Instead, I recommend using **Jira Automation** to track this transition and capture the exact date. Here’s how you can achieve this:

1. **Create a Custom Field**: First, you’ll need to create a custom date field (e.g., "Start Date") to store the date when the story moves to "In Progress."

2. **Set Up an Automation Rule**:
- Go to **Project Settings** > **Automation** and create a new rule.
- Set the trigger to **Issue Transitioned**.
- Configure the condition to capture when the issue moves **FROM "New" TO "In Progress"**.
- Add an action to **Edit Issue** and set the "Start Date" field to **{{now}}**, which will log the exact date and time when the transition occurs.

3. **Resolution (End Date)**: Since you already use the **Resolved** field to capture the end date, there’s no need for additional steps here unless you'd like to store it in a custom field as well.

### Automation Breakdown:
- **Trigger**: Issue transitions from "New" to "In Progress"
- **Condition**: Transition is specifically from "New" to "In Progress"
- **Action**: Set the custom "Start Date" field to **{{now}}**.

With this automation, you'll be able to capture the start date when work begins on each story. You can then combine this with the **Resolved** field for the end date.

Again, I apologize for the confusion earlier, and I hope this solution works for you. Please feel free to reach out if you need further assistance setting it up!