JQL query - End Date 5 Week Days From Now

[Aislin Percival]

Hello Atlassian community friends,

I am trying to write a query that includes only issues with an end date 5 days from now excluding weekends. I can only manage to create it for 5 week days from now (including weekends).

I have looked at plenty of posts about excluding weekends but am still having trouble, so please don't answer simply referencing another post. I have explored them exhaustively already.

Thank you,

Aislin

[Tessa Tuteleers · Accepted solution]

Hi @Aislin Percival , 

I might be not understanding something correctly, but to my calculation an end date 5 workdays in the future will always have a weekend in between (monday -> end date 5 days later is saturday), so next workday is monday again. (assuming you don't have end dates that are on a weekend day.. but whatever query on weekdays will never account for those) 
Just count +7 days

Please let me know if I misunderstood

Tessa

[Aislin Percival]

My apologies, I was counting day of when Jira does not include day of in the count. I need it to be four week days - for instance, a query run on a Monday would return issues with end dates on Friday while one on a Wednesday would return issues with end dates the next Tuesday. 

[Tessa Tuteleers]

Ah, that makes it more complicated indeed! :) 

Let me try though: 

You want to find this:

• Monday + 4 = Friday
• Tuesday + 4 = Saturday -> should be monday after 4 weekdays

For this example, please note that StartOfWeek in Jira is Sunday, so EndOfWeek in Jira is Saturday (you can't change this.)

I then start playing with those weeks stamps  (as you can refer days from it) 

-> if we know EndOfWeek = saturday, then endOfWeek(2d) is 2 days later (next monday)

So if we want to calculate +4 business days, we would do the following: 

( (enddate < endOfWeek() OR enddate > startOfWeek(1)) AND enddate = 4d ) 
OR 
( (enddate = endOfWeek(2d) OR enddate = endOfWeek(3d)) AND enddate = 6d)

So this means: 

end date must be before the end of the week (so at the latest friday) OR after start of week of the next week (so after next sunday) AND the end date must be in 4 days	This part should work for monday (end date is friday so before end of week and +4) thursday and friday (end date is monday or tuesday and +4)
OR
End date must be on a monday (2 days after end of week) OR on a tuesday (3 days after end of week) and it must be 6 days in the future	Second part should work for  tuesday (end date is on saturday so +6) = monday = 4 workdays wednesday (enddate is on sunday so +6) = tueday = 4 workdays

 I tried the logic today (which luckily is a good day (+4 would be saturday) and I get the issues for monday, so it seems to work. 

Can you please test this and see if it fits the bill? 

[Aislin Percival]

Wow! This is amazing. Unfortunately it is bringing up things in the past. I need only things in the future. 

The automation I am building off of it will identify things that are supposed to end in 5 business days and change their priority, so I can't pick up things in the past. 

[Tessa Tuteleers]

Hmm, it shouldn't, as in both parts of the OR the end Date is either 4 or 6 days in the future.

Can you check that you didn't miss any brackets? 

[Aislin Percival]

Yup! I missed a single bracket. 

What a difference it makes. 

Thank you. I will make sure the answer is marked "accepted" and I really appreciate your expertise.

[Tessa Tuteleers]

You're very welcome @Aislin Percival

Enjoy automating!

Cheers, 

Tessa