JQL subtracting dates

I am trying to find issues where the time taken between status changes is within a certain timeframe.

As an example I want to find tasks where the started to completed timeframe is less than one week.

I want something similar to the below JQL - subtract 1 week from done date and get tickets that were started after that point:

status CHANGED to "In Progress" AFTER status CHANGED TO Done - "1w"

2 answers

1 vote

This level of complexity is not reachable with native JQL features.

You could fairly easily achieve that list of issues like this:

With the Better Excel Exporter app, using the "Issues with change history" export, export your issues to Excel
Create a formula that finds the date when the "status" field changed to the new value "In Progress" (if it could happen multiple times, then you should think about precedence: is it the first one or the latest one that matters?)
Same for "Done"
Use a formula that returns TRUE if the first value is less than the second value minus one week (Excel has great functions for date operations)
Filter down to those rows where the formula returns TRUE

The app is here:

The original spreadsheet will be like this:

jira-issues-selected-fields-with-repeating-change-history.png

(Discl. it is a paid and supported app, developed by our team.)

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

0 votes

You cannot achieve what you want with native JQL functionality.

Are you open to using a third party app?

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.