Search code examples
jirajql

Exclude Saturday and Sunday from a range of dates in JQL on Jira Server

I have this query

project = "xyz" AND due > startOfDay() AND due = startOfDay(3d)

What this does is , it gives me the issues where there are exactly 3 days remaining until the due date from today.

i.e if I have a ticket which has a due date of 10th July 2023 and today is 7th July 2023, the issue should show up.

Now from the 7th July to 10th July , there are weekends 8th and 9th July ( Sat and Sun).

Currently what the JQL is giving is including the weekends.

I want the JQL to exclude it.

Expected result: If the due date was on 10th July, it should give me the result when today was 5th of July.

How do I achieve this with JQL?

Solution

  • If your due date is less than 7 days away, you can use the following JQL for a due date three business days from now:

    (duedate < startOfWeek(1) AND duedate = startofday(3d) ) 
OR  (duedate > startOfWeek(1) AND duedate = startofday(5d))

    If you want to check for a due date five business days from now you can use:

    (duedate < startOfWeek(1) AND duedate = startofday(5d) ) 
OR  (duedate > startOfWeek(1) AND duedate = startofday(7d))

    The top line checks a due date before the weekend. The second line checks a due date next week and adds two days for the weekend. If the due date is more than 7 days away, you will need to adjust the numbers by adding 2 days for each weekend you know for sure will be between now and the due date.

    If you want to check for a due date ten business days from now, you have for sure one and maybe two weekends before the due date. You can use:

    (duedate < startOfWeek(2) AND duedate = startofday(12d) ) 
OR  (duedate > startOfWeek(2) AND duedate = startofday(14d))