JQL subtracting dates

[Ronan Treacy]

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"

[Aron Gombas _Midori_]

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

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

1. With the Better Excel Exporter  app, using the "Issues with change history" export, export your issues to Excel
2. 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?)
3. Same for "Done"
4. 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)
5. Filter down to those rows where the formula returns TRUE

The app is here:

https://marketplace.atlassian.com/apps/1212652/better-excel-exporter-for-jira?hosting=cloud&tab=overview

The original spreadsheet will be like this:

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

[Trudy P Claspill]

Hello @Ronan Treacy 

You cannot achieve what you want with native JQL functionality.

Are you open to using a third party app?