How can I calculate between two dates(resolved date and create date) in JQL ?

[Orkun Ovayurt]

How can I calculate between two dates(resolved date and create date) ?

How many days and hours between these days ? Can you help me ?

[Marc - Devoteam]

Hi @Orkun Ovayurt 

Welcome to the community.

You can use smart values for this.

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

You can store this in a custom field ot type text

{{[date1].diff([date2]).prettyPrint}}

[Marc - Devoteam]

HI @Orkun Ovayurt 

Please accept my answer as a solution, if my answer helped to solve or provide a workaround to your request.

This will help other community member trying to solve the same or provide them with a work around

P.S. If the answer is very valuable to you, please share some kudos.

[Yong Yang]

Hi @Orkun Ovayurt 

Welcome to our community.

Solution A:

1.Create a custom field called Lead Time

2.Create an automation rule to calculate the lead time when jira work items are resolved

now() - issue.created = lead time

In the Edit Issue action, set the lead time to above calc value

Solution B:

Some apps maybe.

Hope it helps,

Thanks,

YY Brother

[Hannes Obweger - JXL for Jira]

Hi @Orkun Ovayurt

welcome to the community!

As you can see from the previous responses, this is an area where many Jira users rely on the apps from the Atlassian Marketplace.

E.g., your use case would be trivial to solve 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 issues, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the time between created and resolved, time in [status], time between [status] and [status], and many, many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. Of course, you can also export your data to Excel or CSV in just two clicks.

Any questions just let me know,

Best,

Hannes

[Valeriia_Havrylenko_SaaSJet]

 Hi @Orkun Ovayurt  👋

Welcome to the Community!

Unfortunately, native Jira JQL doesn’t support direct calculations between two date fields like created and resolved. JQL is great for filtering issues based on values, but it doesn’t perform arithmetic operations (like subtracting dates) out of the box.

But here are a few ways to achieve what you need:

Option 1: Use a Reporting App (like Time Metrics Tracker | Time Between Statuses)

If you need to calculate how much time passed between Created and Resolved for multiple issues (in days, hours, or even minutes), apps like Time Metrics Tracker can help. (The app developed by me team.)

You can:

• Set Created → Resolved as a custom metric

• See the exact duration per issue

Here’s an example from the app dashboard:

 

🔍 Option 2: Use JQL filters with relative dates

If you're only looking to filter issues where the time between created and resolved exceeds a threshold (e.g., "more than 5 days"), that’s not doable directly in JQL — but some advanced plugins like ScriptRunner or JQL Search Extensions allow for such queries.

Example (with ScriptRunner):

issueFunction in dateCompare("", "created", "<", "resolved -5d")

Let me know how you want to use the data, dashboard, export, automation, etc and I’ll be happy to suggest the best solution!

[Birkan Yildiz _OBSS_]

Selamlar @Orkun Ovayurt Bey,

If you are open to using a marketplace app, Timepiece - Time in Status for Jira is what you are asking for.
Its Duration Between Statuses report is designed to do exactly what you need. You can simply select the first status (e.g., To Do) as your start point and last status (e.g., Resolved) as your end point, and the report will calculate the total duration for each issue.

This gives you your "Resolution Time" or "Lead Time" without needing any complex workarounds. Also, you can see the total time in minutes, hours, days, weeks, etc.


You can check Timepiece - Time in Status for Jira on the Atlassian Marketplace. Hope this helps you get the data you're looking for! Please feel free to reach out if you have further questions.

Disclosure: I'm on the team that makes Timepiece - Time in Status for Jira.