Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Jira Cloud for Sheets : use "AND Status WAS ... ON dd/mm/yyyy


With Jira Cloud / Google Sheets integration is it possible to use the filter "AND Status WAS ... ON dd/mm/yyyy? 

I have tried  =JIRA("project=MYPROJECT AND status was New on "10/08/2019" order by created DESC";"issuetype,status";0;10)

 but I get a formula analysis error.

2 answers

1 accepted

1 vote
Answer accepted

Hi Emmanuel,

I can see three conflict points in the formatting, first you are using semicolons ";" as the seperator and this should be a comma ",".

Next, the quotation marks around the date need to be escaped and in the "Jira for sheets" application, it uses a second set of quotes to do this, so ""date""

And finally with the date once the double quote escape character is in place the order of the date is incorrect and if you run it in the current formatting "dd/MM/yyyy" with the double quotes you will get this error:

Date value '10/08/2019' for predicate 'on' 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'.

So if you reorder the the date to be ""yyyy/MM/dd"" like the following you should be all set:

=jira("project=MYPROJECT AND status was New on ""2019/08/10"" order by created DESC","issuetype,status",0,10)


Hi Earl,

Thanks a lot for your answer. It works very well.


I have one more related question : I just try to use this formula with a date located in [A;2] in a googlesheet (assuming that 2019/08/10 is in [A;2])

I have tried :

=jira(CONCATENATE("project=MYPROJECT AND status was New on ",CHAR(34),CHAR(34),TEXT(A2,"yyyy/mm/dd"),CHAR(34),CHAR(34)),"status",0,3)

The error message is :

Error in JQL request : 'OR' or 'AND' is anticipated but '2019' is mentioned instead (line 1, character 43) (line239)

Like Earl McCutcheon likes this

For traceability : I have found the answer to my second question.

2 remarks :

  • the character "/" in the date seems creating an issue. Replace it by "-" can fix it.
  • the quotation marks seem not necessary in this case.

The right formula is: 

=jira(CONCAT("project=MYPROJECT AND status was New on ",TEXT(A2,"yyyy-mm-dd")),"status",0,3).

Thanks again to Earl for the support provided.

Like # people like this

HI Emmanuel,

Thanks for the confirmation and follow up with the second solution I'm sure it will help out others if they run into this thread.

And just to provide a little clarification on whats occuring is that the main thing you run into is that the input for the JQL needs to be recognized as a string so throwing in a concatenate to combine the text inside quotes and the cell references outside quotes does the trick for a cell referance that contains text, but to complicate this a bit further the DATE value when passing the field containing 2019/08/20 is formatted as a date table rather than a string so when concatenated converts to a string value of "43697", so passing it in as a TEXT value via text(cellReference,"yyyy/mm/dd") clears this up for the concatenation to recognize the date as a plain text string rather than the date value conversion that is originally taking place.

Overall concatenate() is super powerful here but you just have to look out for special formatting applied to referance cells and thanks again for the extra details


Like emmanuel_dolley likes this

Hi Earl,

Thank you for this clarification. It will help me for my next queries too.

Best regards,


Hi Emmanuel,

Sure thing, and happy to help out.

Also, I was playing around with this query a bit more as it was bugging me that CONCAT() only allowed passing 2 options where CONCATENATE() would allow for as many as you wanted to pass in, but the TEXT() variable would not come through when passing into the CONCATENATE() formula.

As TEXT() would not work in CONCATENATE(), what I was able to come up with is to set up a Year, Month, Day, & "\" value in independent fields that can be used for the cell referance pass through to the date to the JQL statement I also added in a Project and Status field as well to make the full JQL editable from the field referance values

Side Note: For the references to project and status I'm also thinking you could get even more elaborate and use drop downs with preset values in these fields to make it look nicer as well and have quick toggle options rather than editing the filter directly but I did not make it that far

Then nested a second CONCATENATE() in the first to combine the variables, and it works really well. 

See the following screenshot as an EXE:

Screen Shot 2019-08-22 at 8.48.06 AM.png

The Query I set up has the referance Cells set up as follows:

  • a1 = Project name
  • a2 = Status name
  • b1 = Year 
  • c1 = Month
  • e1 = Day
  • d1 = /

And the Query:

=jira(CONCATENATE("project=",A1, " AND status was ", A2, " on (""",CONCATENATE(B1,E1,C1,E1,D1),""") order by created DESC"),"issuekey,summary,status",0,10)

And you could just as easily set up the field's as a variable to pass in as well and do a coma separated list of fields names for your column values in a single field.


Like emmanuel_dolley likes this

Hi Earl,

Thanks you a lot for this new answer, that's really helpfull and simplify my queries.

I can perform them in a single step now. Thanks again,

Best regards


Like Earl McCutcheon likes this

Hi Earl,

Thanks a lot for your answer. It works very well.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Jira

Online AMA this week: Your project management questions answered by Jira Design Lead James Rotanson

We know that great teams require amazing project management chops. It's no surprise that great teams who use Jira have strong project managers, effective workflows, and secrets that bring planning ...

172 views 1 6
Read article

Atlassian Community Events