Formula to compare Due Date to Resolved Date

[Serena Barker]

Hi,

I'm trying to identify the issues that were resolved before the due date and I've seen in the community that it may not be able to be done.  I'm trying to use a simple filter where - 

Resolved date <= Due Date for the ones completed in time 

Resolved date > Due Date for the ones completed late

Can anyone suggest an easy workaround to make this work please?  I am not a developer, just trying to do a simple filter condition.

TIA

Serena

[Valeriia_Havrylenko_SaaSJet_AbcSite]

Hi @Serena Barker  👋

JQL doesn't offer a 'date compare' out of the box. You can compare to some relative date functions such as startOfDay(-3d) and such. But comparing two date fields like Resolution date and Due date is not possible.

As a work-around, you could create an automation rule, scheduled to run each month. This rule would compare the resolved date with due date. And in case the resolved date is > due date, the rule can add a label 'resolved_after_due'. 

Once this rule has run, you can create a report filtering on issues with the label 'resolved_after_due'. 

In the automation rule you can use an Issue feld compare. For example, the rule can trigger on transition to Done. 

And then the rule can check if the Due date is BEFORE the moment o firing the rule. 

If an app from the Atlassian Marketplace should become an option for you, you may want to have a look at the app that my team and I are working on, Time Between Statuses add-on. 

Set up Warning time limits and Critical time limit to prevent tasks past their due date. If too much Time is spent on a specific issue, the system will send an email notification to you or any other user when a ticket is stuck in a status. You will also see it in your workflow. 

Add-on developed by my by SaaSJet team. 

We are offering you a 30 days of free trial, and you can also book a demo with our specialist.
Hope this helps!

[Serena Barker]

Hi @Trudy Claspill  love the cat profile pic btw :)

Thanks for your response.  I was hoping to achieve it without a third party app if possible.

The goal is to kpi how many tasks (or % of tasks) were completed on time vs after due date.

I did see some comments on other posts about using automation rules, but I must admit I was a little lost on how it worked to achieve the results.  Any help you could give me would be greatly appreciated.

[Trudy Claspill]

Hello @Serena Barker 

Comparing one issue date field to another issue date field in a JQL filter is not supported natively. It could be possible with the addition of a third party app. Are you open to considering purchasing a third party app?

What do you want to do with the results when you get them? It may also be possible to get what you want through an Automation Rule, depending on what you want to do with the results.

[Trudy Claspill]

Hello @Serena Barker 

The information provided by @Valeriia_Havrylenko_SaaSJet_AbcSite is one example of how you could use an Automation Rule to identify issues resolved before or after the due date and add a label value to each issue to help you get the lists for your reports. Here is an example of what they described.

1. Create a rule that uses the Schedule trigger. Set the frequency to what seems reasonable to you (maybe once per day). 

2. Below the Occurrence portion of the trigger configuration is a section to tell the rule that you want to run a JQL filter to get issues each time the rule runs. Later in the rule you will add steps that will run against each issue retrieved. In your case you would want the issues that have a Resolved Date and a Due Date, and that don't have either of the labels that tell you the issue was resolved after the Due date or before the Due date.

The JQL I would recommend is:

resolutiondate is not empty and duedate is not empty and Labels not in ("resolved-after-due", "resolved-before-due", "resolved-when-due")

Make sure to uncheck the box below the JQL.

3. Next you need to add steps to evaluate the relationship between the Resolved date and the Due date. Here I need you to provide a clarification - If an issue is resolved on the same day it is Due, what label do you want to apply to the issue?

I would use an If-Else Condition here.

Select the IF Condition option, then the IF or ELSE condition.

You'll build up steps that logically perform this:

If Resolution is before Due
  Then add label "resolved-before-due"
Else if Resolution is after Due
  Then add label "resolved-after-due"
Else
  Add label "resolved-when-due"

Because the Resolved field is a date/time field and Due date is just a Date field, you have to use the Smart Values condition to format them each to a simple Jira Date so they can be compared.

The above rule would look at all the issues in the target project when both fields are set and the Labels field has not already had a label added to indicate the relationship between Resolved and Due.

If either of those fields changed, this rule would not update the issue again unless the "resolved-..." Label was removed.

You might want a second rule that is triggered by the Due date field being changed, to then remove the "resolved-" label if it is present.

And you might want a third rule that is triggered by a change to the Resolution, to then also remove the "resolved-" label.

Then the next time the schedule rule ran, it would re-evaluate those changed issues.