JQL subtracting dates

Question

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_ · Answer

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:

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 · Answer

Hello @Ronan Treacy

You cannot achieve what you want with native JQL functionality.

Are you open to using a third party app?