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. 

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

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

[Isadora_Nichetti]

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

[Valeriia_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

[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

[Valeriia_SaaSJet]

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

[Valeriia_SaaSJet]

---

Sorry, this is Jira's glitch.

Hope you found the answer to your question.

[Emre Toptancı _OBSS_]

Hello @Ash Viceer,

If you are OK with using a marketplace app for this, our team at OBSS built Time in Status 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. 

 

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. 

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.

  

https://marketplace.atlassian.com/apps/1211756/

EmreT