You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
The plug-in opens a window on the right and gives me the opportunity to enter JQL directly into the "JQL Query" text-entry field. Is there a way to use cell references in that query from another tab?
Specifically I have another tab (named "Weeks") that contains a from- and to-date fields. I would like to use them in my JQL. Something similar to:
status changed to "Dev Complete" during("&Weeks!b6", "&Weeks!c6")
Hi @Tom Gagne
Please take a look at the examples in this question's solutions, as I believe you are missing some ampersands to make the query concatenate correctly and use of nested quotation marks:
Bill, good point. If I try without quotes it complains about the "!" If I try with quotes I get:
Date value 'Weeks!$B$6' for predicate 'during' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.; Date value 'Weeks!$c$6' for predicate 'during' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.
Ah...that second thing is the Jira/Excel date format mismatch. Perhaps try this to solve both issues at once:
Create Excel named variables which point to your cells, and in the named variable use Excel's TEXT() function to convert to data format Jira is wanting and forcing to "text" in one step. Then reference the named variables in the JQL.
Tom, I have not found any documentation for these spreadsheet addin features...just some community posts on them.
And apologies if I misunderstood that error. I thought it was indicating it resolved the cell reference and that the date/time format was wrong. It is possible the exclamation point is causing the problem and that is why I suggested using an Excel named reference. Please look here for information on using the name manager in Excel: