Card color based on difference of two date fields? (best practice)

[Eric Haas]

Hi there,

I'm stuck. 🤔

I'm looking for a way to define a card color depending on the difference of two date fields.
Let's assume there are two date fields - "start date" and "end date".

How can I easily color tickets on the board if "end date" - "start date" > 90 days?

Can someone please point me to the solution or give me a hint?

Thanks and best
Eric

[Kristian Walker _Adaptavist_ · Accepted solution]

Hi Eric,

To define JQL to compare two dates, you cannot do this out of the box and will need to use a third-party app such as ScriptRunner for Jira Cloud.

You can then use the Enhanced search page to define a query using the dateCompare function to return issues that match your calculation of end date" - "start date" > 90 days.

Once you have defined your filter on your enhanced search page you can use it in the card colour query by following the steps below.

1. Save the filter and share it (This creates a copy of the filter as a standard Jira filter where we synchronise the list of issue keys returned by the enhanced search filter.)

2. Navigate to the card query section of the board and reference your filter in the JQL box using the JQL like below.

filter='NameOfHisSharedESFilter'

If you want just the enhanced search JQL functions, then we have the standalone Enhanced Search app that provides these at a smaller cost.

I hope this helps.

Regards,

Kristian

[Eric Haas]

Great explanation, @Kristian Walker _Adaptavist_ - thanks for that! 👍

I'll check and discuss it with our Jira admin and then I might come back to you... if I may😉

[Eric Haas]

Fyi, we checked it and found several issues:

1. dateCompare does not work with customfields (it somehow assumes these fields are no date fields - although they are)
2. dateCompare does not work with field names consisting of two words (e.g. "end date") - regardless how you format the text -> see Exact searches (phrases)

To solve the issue we did the following:

1. We used expression - instead of dateCompare
2. We used the "customfield_XXX" name
3. To substract two dates (here: "end date" - "start date") we needed to convert the wanted 90d into milliseconds (here: approx. 7,746,000,00 ms).

So here's the final JQL:
issueFunction in expression("project = ABC", "customfield_XXX - customfield_YYY >= 7746000000")

A bit strange but it worked finally - at least for us. 😉

Thanks for the initial input, @Kristian Walker _Adaptavist_ !

[Kristian Walker _Adaptavist_]

Hi Eric,

Thank you for the feedback and for sharing your final solution.

Just for reference, I have checked, and using the enhanced search Query Builder you can select custom date fields to use in the date compare function, as shown below.

I'm glad we helped you get a working solution to your requirements.

Regards,

Kristian

[Eric Haas]

Thanks for the additional information.
That might solve the issues but we rather used Jira's issue navigator.

New try next time 😉

Kr,
Eric

[Aron Gombas _Midori_ · Accepted solution]

You cannot use JQL to test the difference between two dates.

Instead, you could implement an automation that:

1. Either runs periodically and if the time difference is greater than 90d, then adds a label to the issue (and removes if it is not true anymore).
2. Or updates a new custom field that stores the difference every time when one of the start or end date fields changes its value.

Then you can use a JQL to test for the label (approach 1) or to test for the custom field value.

[Eric Haas]

Thanks Aron! 👍

Does 2. only work with automation?
Fyi, we currently use Jira on-prem without automation.

[Aron Gombas _Midori_]

Automation is available for Jira Data Center, yes! Also, you can implement calculated custom fields with other apps, for example with ScriptRunner.

Maybe the question is are you already using any app to implement automations in any way?

[Aron Gombas _Midori_]

Oh, I just realized that Automation is already part of Jira Data Center (and there is no addition costs associated with it)!

[Eric Haas]

Many thanks also to you @Aron Gombas _Midori_ ! 👍
I'll check with our Jira admin. 👍

[Nikola Perisic · Accepted solution]

Hello @Eric Haas 

You can do this by selecting the method of queries:

Based on a JQL query you can define the color of start and the end dates.

[Eric Haas]

Hi @Nikola Perisic 

Yes, thanks a lot...!
I know where to define it... but - at least for me 🤔 - I can't define a formula similar to this:
("end date" - "start date") > 90d

Maybe I just don't get it?
Or do I need an additional custom field first which calculates the difference of the dates?

[Nikola Perisic]

With JQL this wouldn't be possible, rather with an automation rule. You would create two custom fields for start and the end date which will fetch when the issue has started and when the issue has ended and to do the calculation:

{{issue.fields.customfield_10074.diff(issue.fields.customfield_example)}}

[Eric Haas]

Many thanks for the info, @Nikola Perisic ! 👍

Just for my understanding:
Do automation rules only work with Jira Cloud or do they also work with Jira (on-prem)?

[Nikola Perisic]

No problem @Eric Haas 

You should be having the Automation for Jira app, if not you can navigate to the marketplace and search for this app.

Also you can check the Manage apps field to see if there is Automation for Jira installed.

[Eric Haas]

Thanks again - I'll take a look for it! 👍