nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Tessa Tuteleers

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

All comments

Tessa Tuteleers

Hi @Aislin Percival ,

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)

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-work-management

jira-work-management-cloud