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

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.)