nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Alana Fernando

hi @danielho95,

welcome to the community,

would this be helpful to you? 😊

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

All comments

Alana Fernando

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

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

custom-field

jira

jql