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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ah, that makes it more complicated indeed! :)
Let me try though:
You want to find this:
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:
|
This part should work for
|
OR |
|
|
Second part should work for
|
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.