How can I find the tasks that are moved to Done Status after the DueDate field in JIRA.

Vinutha GR_hm65q3

Suppose the due date is 10-Nov-23 and today is 10-Dec-23 and if the status is "Open". The following query pulls the record.

status != Done and duedate <= now(). 

But if the status is moved to "Done" on 12-Nov-23, the query doesnt pull this record.

i need something like status changed to Done after duedate.

I dont see an option to compare with JIRA field 

David Friedrich

Hi @Vinutha GR ,

if I understand correctly you are trying to look for issues that were closed (= moved to Done) after they had reached their due date. 

 

Unfortunately Jira's native JQL doesn't provide a direct way to create a filter that checks if the status was changed to "Done" after the due date in a completely generic manner. Jira's JQL is designed for querying current data, and historical change comparisons have limitations.

You could consider creating a custom field to store the historical status changes along with timestamps and then use that custom field in your JQL queries.

Here is a general guide on how you might set this up:

1. Create a Custom Date-Time Field:

First, create a custom field of type "Date-Time" in Jira where you want to store the timestamp of the status change.

2. Set Up a Jira Automation Rule:

Use Jira Automation to create a rule that triggers on the issue transition you're interested in (e.g., when the status changes to "Done"). Here's a simplified example:

1. Go to Jira Settings > System > Automation > Rules.
2. Create a new rule.
3. Set the trigger condition to "Status changed" and specify the relevant status (e.g., from any status to "Done").

3. Add an Action to Copy Timestamp:

In the automation rule, add an action to update the custom date-time field with the current timestamp. This may require using a combination of Jira Smart Values and possibly custom scripts.

For example, using the "Edit Issue" action:

• Choose the custom field you created.
• Use a Smart Value for the timestamp, such as {{now}}.

4. (Optional) Add Conditions:

You might want to add additional conditions to the rule to ensure that the custom field is only updated under specific circumstances (e.g., if the custom field is empty).

Once status change to done is tracked in a custom date field you still can not compare that to the due date field via JQL. But in your issue search list view you can now add that field in a new column and compare it directly with the duedate. Or export it to Excel or or...