Calculation of dates between two jira columns

[Del_Singh1]

Hi All,

Currently we have the following Jira Columns:

Due Date (day/month/year) = When an 'item' should be fixed.

Number of days past due date = Days Past Remediation

Days Past Remediation = ?

So, trying to currently understand how the calculation for due date and any date past that = days past remediation.

All in all wondering if anybody within the community has had a similar issue and has an example formula I can use to input within JQL / or using the GUI functionality?

Any help would be much appreaciated :)

Thanks, Del.

[Dimitris Sylligardakis · Accepted solution]

Hi @Del_Singh1 ,

Hope this helps steer you in the right direction:

First, you need to create a custom field to store the "Days Past Remediation" value.

1. Go to Jira Administration > Issues.
2. Under Fields, click on Custom fields.
3. Click on Add custom field.
4. Choose Number Field (since you want to store the number of days) and click Next.
5. Give it a name (e.g., "Days Past Remediation") and click Create.
6. Add the custom field to the appropriate screens.

Next, you need to create an automation rule to calculate the number of days past the due date.

1. Go to Project Settings > Automation (or System Settings > Automation Rules for global rules).
2. Click on Create Rule.
3. Choose a trigger, such as Field Value Changed and select Due Date. This will trigger the rule whenever the due date is set or changed.
4. Add a Condition to ensure the due date is in the past. Use the Advanced Compare Condition:
   • First value: {{now}}
   • Condition: is after
   • Second value: {{issue.duedate}}
5. Add an Action to calculate the number of days past the due date. Choose Edit Issue and set the custom field "Days Past Remediation" to the following smart value:
   • {{now.diff(issue.duedate).days}}
6. Save and publish the rule.

Now that you have the "Days Past Remediation" field populated, you can use JQL to query issues based on this field.

For example, to find all issues that are past their due date by more than 5 days, you can use:

"Days Past Remediation" > 5

(Unfortunately, JQL itself does not support direct date arithmetic or calculations. The automation rule described above is necessary to populate the "Days Past Remediation" field, which you can then use in JQL queries.)

[Del_Singh1]

Hi @Dimitris Sylligardakis 

No Advanced Compare condition seen unfortunately..

So, as you can see we’re on step 4. But we’re unfortunately stuck on this part. Is the issue to do with Jira versioning? I.e. to see the advanced compare condition.

[Bill Sheboy]

Hi @Del_Singh1 -- Welcome to the Atlassian Community!

For Jira Cloud automation rules, the "advanced compare condition" was renamed to "smart values condition".

Kind regards,
Bill

[Valeriia_Havrylenko_SaaSJet_AbcSite]

Hi @Del_Singh1  👋 
Welcome to the community!

For your needs I would recommend trying out our another add-on Time Between Statuses. The add-on is specifically designed to calculate dates between two Jira columns.

You can also set up highlights of values that exceed the permissible level, and in this case, you can receive issue and email notifications.

 Benefits:

• Identify Bottlenecks: Quickly see where issues are getting stuck in your workflow.
• Optimize Processes: Use data to streamline processes and improve efficiency.
• Enhanced Reporting: Gain deeper insights into your team's performance and workflow effectiveness.
• Improve Accountability: Make data-driven decisions to hold teams accountable and drive continuous improvement.
  
  

Add-on has 30-days trial version, free up to the 10 users and developed by my SaaSJet team.

I hope this helps 😊

[Hannes Obweger - JXL for Jira]

Hi @Del_Singh1

welcome to the community!

Just to add to @Dimitris Sylligardakis' great answer: If you're open to solutions from the Atlassian Marketplace and don't want to spend your Automation budget on this, this would also be easy to do using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called smart columns that aren’t natively available, including the time to due date.

This is how it looks in action:

As you can see above, you can easily sort and filter by the time to due date, and also use it across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

This all just works - there's no scripting or automation whatsoever required.

Any questions just let me know,

Best,

Hannes