Calculation of dates between two jira columns

Del_Singh1
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 20, 2024

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.

3 answers

1 accepted

1 vote
Answer accepted
Dimitris Sylligardakis May 20, 2024

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}}
    • Conditionis 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
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 23, 2024

Hi @Dimitris Sylligardakis 

 

1.png

 

No Advanced Compare condition seen unfortunately..

 

2.png

 

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 23, 2024

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

1 vote
Valeriia_Havrylenko_SaaSJet
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 22, 2024

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.

image.png

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

image.png

 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 😊

0 votes
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 21, 2024

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:

time-to-due-date.gif

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

Suggest an answer

Log in or Sign up to answer