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; } } }

How to Exclude Weekends from JQL Query

Christi Evans

Question: How to exclude Saturday & Sunday from JQL filters.

Backstory: We use a "Resolution Time" gadget on Jira dashboard that illustrates how many days it took to get bugs resolved, on average.

It currently runs off this simple query: project = Puppies AND issuetype = Bug

We do not want Saturday & Sunday counted in the resolution time. We want Monday - Friday, business days only.

Additional Information:

It's Jira team-managed project

It's not a Jira Service Project (no SLA options afaict)

Want to exclude all weekends, not specific weekends.

Accepted answers

All comments

Bill Sheboy

Hi @Christi Evans

Frist thing: I recommend checking if that gadget is providing accurate enough data for your needs, as is. It seems designed to show Lead Time (time from create to completion), and my quick testing seems to show some reporting errors for that.

Next, there are no built-in JQL features to remove the weekend days from a date range.

I believe you have at least three alternatives, depending on how frequently you need this information:

need this one time:
- export the issues to a spreadsheet, and perform the calculations to remove the weekend dates between the range of Created and Resolved.
need this often, and have money to spend:
- investigate marketplace addons for dashboard gadgets, or
- investigate marketplace addons for scripted fields, compute the date difference, and then use another marketplace gadget for the reporting
need this often, and do not have money to spend on addons:
- add a custom field for the value, and an automation rule to compute the difference in business days with {{issue.created.diff(issue.resolved).businessDays}} to set the field
- add one of the free gadgets which can plot this

Kind regards,
Bill

Emre Toptancı [OBSS]

Hello @Ash Viceer,

If you are OK with using a marketplace app for this, our team at OBSS built Timepiece - Time in Status for Jira for this exact need. It is available for Jira Server, Cloud, and Data Center.

Time in Status mainly allows you to see how much time each issue spent on each status.

tisCloud_StatusDuration_LeadTime_with Estimates.png

The app allows you to define multiple custom calendars, each with its own working days, working hours, and holidays.

The app has Consolidated Columns feature. This feature allows you to combine the duration for multiple statuses into a single column and exclude unwanted ones. It is the most flexible way to get any measurement you might want. Measurements like Issue Age, Cycle Time, Lead Time, Resolution Time etc.

For all numeric report types, you can calculate averages and sums of those durations grouped by the issue fields you select. For example total in-progress time per customer (organization) or average resolution time per sprint, week, month, issuetype, request type, etc. The ability to group by parts of dates (year, month, week, day, hour) or sprints is particularly useful here since it allows you to compare different time periods or see the trend.

tisCloud_StatusDuration_LeadTime_Average_TimeGrouped.png

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It supports both Company Managed and Team Managed projects for Jira Cloud.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs (also available on issue details views on board view). All these options can provide both calculated data tables and charts.

Timepiece - Time in Status for Jira

EmreT

Valeriia_Havrylenko_SaaSJet

Sorry, this is Jira's glitch.

Hope you found the answer to your question.

Valeriia_Havrylenko_SaaSJet

also, i can advice you to keep an eye on solutions in this discussion https://community.atlassian.com/t5/Jira-Core-Server-questions/Exclude-Weekends-in-JQL-Query/qaq-p/2005657

Isadora Kniess

Its nice solutions offered. I hope you choose one that best suits for your needs.

Valeriia_Havrylenko_SaaSJet

I hope you choose one that best suits for your needs.

Mariana_Pryshliak_SaaSJet

Hello @Christi Evans

If you're ok with using third-party add-ons, I guess trying Time between Statuses (server version) which allows excluding non-working days or hours from the calculation in a few clicks.

Знімок екрана 2023-03-24 о 16.47.26.png

This add-on is developed by my SaaSJet team and has a 30-day free trial version. Please, let me know if you have any questions