Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

JQL query - End Date 5 Week Days From Now

Aislin Percival
Contributor
August 20, 2024

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

1 answer

1 accepted

1 vote
Answer accepted
Tessa Tuteleers
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 20, 2024

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
Contributor
August 20, 2024

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 20, 2024

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
Contributor
August 20, 2024

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 20, 2024

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
Contributor
August 20, 2024

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.

Like • Tessa Tuteleers likes this
Tessa Tuteleers
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 20, 2024

You're very welcome @Aislin Percival

Enjoy automating!

Cheers, 

Tessa 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
atlassian, ace, atlassian community event, donation, girls who code, women in tech, malala fund, plan international, kudos, community badge, badge, atlassian badge, International Women’s month, International Women’s Day, women's month, women's day

10 for Change at Atlassian Community Events

Show up and give back by attending an Atlassian Community Event: we’ll donate $10 for every event attendee in March!

Join an Atlassian Community Event!
AUG Leaders

Upcoming Jira Events