JQL Filter for date difference larger than 180days

[danielho95]

Hello, 

I am attempting to write a filter which returns the projects which were closed between June 1, 2018 and now(), but also have a difference of 180d between the custom fields "Start Date" and "Closure Date".. that is "Closure Date" - "Start Date" >= 180d. 

This is what I have so far

project = CIP AND issuetype = Project AND status = Closed AND "Project Closure Date" >= 2018-6-1 AND "Project Closure Date" <= now()

Missing the 180d diff between the custom dates (start and closure)

I don't believe this arithmetic is available in JQL .. Are there any alternatives or will I have to export into Excel for this computation?

thank you

[Alana Fernando · Accepted solution]

hi @danielho95,

welcome to the community,

would this be helpful to you? 😊

"Project Closure Date" >= "-180d"

[danielho95]

Hi Alana, 

Thanks for the warm welcome. Unfortunately that has not worked. What I require is something that calculates the day difference between "Project Closure Date" and "Project Start Date" (both custom fields) and only returns those that have a day difference greater than or equal to 180 days. 

Thank you 

[Alana Fernando]

correct me If I am wrong, you want to identify projects that took 180 days to end? 

[danielho95]

Took greater than or equal to 180 days to end, with that value being calculated by comparing the custom fields "Project Closure Date" and "Project Start Date". 

[Alana Fernando]

could this be used? 

"Start Date" >=  "-180d" AND "Project Closure Date"  >= "-180d"  

[danielho95]

That addition didn't change the filter results but I tried 

"Start Date" <=  "-180d" AND "Project Closure Date"  >= "-180d"

and it reduced the number of projects returned. Will look into the accuracy of the results now. 

[danielho95]

Alana, I realised that when I reversed the inequality for "Start Date", it was actually the correct filter, just that it was missing 2 entries due to the fact that "Start Date" was left empty in those projects (when I calculated in excel, I used an amended start date which used "Created Date" when "Start Date" Was not there). 

Thank you for your help!

[Alana Fernando]

Were you able to solve the issue?

[danielho95]

Still figuring out why there are (slight) inconsistencies between your filter results and my Excel result. 

[Payne]

If you have ScriptRunner installed, you can make use of the DateCompare() function.

https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_datecompare

You could do something like:

issueFunction in dateCompare("project = CIP AND issuetype = Project AND status = Closed AND 'Project Closure Date' >= '2018-6-1' AND 'Project Closure Date' <= now()","Project Closure Date > Start Date + 180d")

[danielho95]

I will have to look into getting ScriptRunner installed for the organisation.

A bit off topic, but we are quite new to Jira. Just wondering if you think ScriptRunner is a worthy add on. 

[Payne]

We use ScriptRunner a fair amount and do find it to be a very worthy add-on.

-Payne