Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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 Apps & Integrations

Send an Email or Publish to Confluence - What should you do with your release notes?

Background When you hear the words ‘Release notes’, almost always you think of an unsolicited email from a software vendor. But I am here to tell you that from our data, sending release notes via E...

112 views 1 2
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you