Is It Calculate the Mid-Point Between two Dates in JQL

[Ryan Darley]

I would like to set up an automation rule that creates a new child issue on the midpoint between two date fields on the parent issue. I plan to execute the automation daily at 8am and filter for issues that are at that midpoint on that day. Is it possible to filter issues based on a calculation of midpoint between two dates?

ie. Parent issue has a start date of 1/1/2024 and end date of 1/8/2024 then it would appear in the JQL results on 1/4/2024 and generate the appropriate child ticket

[Trudy Claspill · Accepted solution]

Hello @Ryan Darley 

What if the start date or end date on the issue changes, and that changes the calculation of the midpoint?

You can use the diff function to get the difference between two dates:

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-difference---

If you then use a math function to divide that by 2, that will give you half of that time period.

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

You can then take that and add it to the start date with the plus function to find the date that is the midpoint.

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-plus-unit---

And then compare that date to the current date on which you automation rule is running.

Or you might want to have a rule that does that calculation and stores that date in another field. Then use a separate automation to execute a JQL filter to look for issues where that midpoint date field matches the current date.

But you need to consider what happens if the start date or end date values are changed or blanked out. What if the midpoint has already been reached for a given issue and the child issue created, but then the dates are changed and the midpoint is pushed out? Or the dates are changed before the midpoint is reached, and now the midpoint is already in the past?

[Ryan Darley]

Trudy,

This is exactly what I needed. Thank you so much for the advice, I really appreciate it!