How to exclude weekends in JQL?

[Celina Kim]

Hello,

I added a report in a team dashboard showing any issues in Waiting, Code Review and QA over 1 business day.

Here is the JQL I put: 

project = "SSS Kanban" AND ((status = "Code Review" AND NOT STATUS CHANGED AFTER STARTOFDAY(-12h) ) OR (status = "Ready for QA" AND NOT STATUS CHANGED AFTER STARTOFDAY(-12h) ) OR (status = "Waiting") AND NOT STATUS CHANGED AFTER STARTOFDAY(-12h) OR (status = "Code Review Completed" AND NOT STATUS CHANGED AFTER STARTOFDAY(-12h) OR (status = "QA Passed" AND NOT STATUS CHANGED AFTER STARTOFDAY(-12h) ))) AND type != Sub-task ORDER BY updated ASC

However, I don't know how to exclude weekends. Can anyone help please?

[Bill Sheboy]

Hi @Celina Kim 

Short answer: That is not possible with the built-in features of JQL.  There are marketplace addons to JQL which probably support this type of query.  And there are "workarounds" to check updated (rather than just status changes) for this type of query.

Here is a suggestion from 2010 to add this feature to JQL, although about 10 years ago Atlassian indicated it was not on their roadmap: https://jira.atlassian.com/browse/JRACLOUD-22506

Kind regards,
Bill

[Vitalii_Bobak_SaaSJet]

Hi @Celina Kim  👋

You might have to create your own solution or use a third-party app to access this data. So if you consider installing third-party add-ons to help, you can try Time in Status.

The algorithm for excluding weekends is quite simple.

First, you must set up your work calendar - add days off, breaks, holidays, etc.

In the generated report, select the required project. To generate a report, select the Created by a specific date range filter—select a date three days before the current one. You can also specify the time period you need.

In the report, select the Business Days data format. This will prevent the calculation from considering non-business hours.

With add-on, you can create metrics tailored to your unique workflow and export the data for deeper analysis if needed.

You can also book a live session or contact us at Support - we'll help you with add-ons. 

Add-ons developed by my team.

I hope you find this helpful 🚀

[Mehmet A _Bloompeak_]

Hi @Celina Kim

As far as I know, you cannot exclude weekends in JQL. You can use a third party app to exclude weekends.

If you prefer using a marketplace app, you can try Status Time Reports app developed by our team.

It mainly provides reports and gadgets based on how much time passed in each status and status durations are calculated according to the working calendar you define. Once you enter your working calendar into the app, it takes your working schedule into account. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days.

Here is the online demo link, you can see it in action and try without installing the app.

If you are looking for a completely free solution, you can try the limited version Status Time Reports Free.

If you need any help, feel free to contact us via support@bloompeak.io or schedule a demo with us.

Hope it helps.

[Madhu_RVS]

Hi @Celina Kim 

As suggested by everyone, a mktplace app can help here . if you would be interested in the same, please try out an add-on we have developed for the same use case.

Time in Status Reports 

The app allows to set your working calendar with the option to exclude weekends. And then you can easily filter issues for the time in status for a specific duration.

More details here

Disclaimer : I work for the vendor who built this app

[Gizem Gökçe _OBSS_]

Hello @Celina Kim ,

Unfortunately, excluding weekends directly using JQL is not possible with the built-in features. However, you can achieve this with Timepiece – Time in Status for Jira,developed by my team at OBSS. Timepiece offers advanced reporting capabilities, including custom calendars that allow you to exclude weekends and holidays from your calculations.

By using Timepiece, you can configure a custom calendar based on your team's working hours, and then generate a report showing issues in specific statuses, such as Waiting, Code Review, and QA, excluding weekends from the calculation.

If you'd like to explore this solution further, feel free to check out Timepiece on the Atlassian Marketplace or if you'd like more details you can also schedule a live demo. We will provide a comprehensive overview of the application and address any inquiries you may have.

Hope it helps,

Gizem

[Hannes Obweger - JXL for Jira]

Hi @Celina Kim

if you're open to solutions from the Atlassian Marketplace, you may want to have a look at 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 issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of so-called history columns that aren't natively available, including the time since status chanced. You can configure this column to consider a work time calendar, e.g. to exclude non-working days.

This is how it looks in action:

As you can see above, you can easily sort and filter by the time since status changed, and also use it across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

[Neil Fletcher]

Hi @Celina Kim 

A variation of this might work for you... I added the following JQL against the due date.

AND due < endOfWeek()

When this is added, the issues returned do not include Saturday or Sunday

When it's removed, issues due Saturday or Sunday are visible.

[Neil Fletcher]

...adjusting the JQL

AND due NOT IN (endOfWeek(), endOfWeek("+1d"))

this should always skip Saturday and Sunday

(my earlier attempt skipped everything after the coming Saturday 😱)