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; } } }

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

Accepted answers

Nikola Perisic

Hello @Eric Haas

You can do this by selecting the method of queries:

Screenshot 2024-02-07 at 10.17.59.png

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

Aron Gombas _Midori_

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

Instead, you could implement an automation that:

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).
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.

Kristian Walker _Adaptavist_

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

All comments

Nikola Perisic

Hello @Eric Haas

You can do this by selecting the method of queries:

Screenshot 2024-02-07 at 10.17.59.png

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?

Aron Gombas _Midori_

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

Instead, you could implement an automation that:

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).
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.

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)?

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)!

Kristian Walker _Adaptavist_

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:

dateCompare does not work with customfields (it somehow assumes these fields are no date fields - although they are)
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:

We used expression - instead of dateCompare
We used the "customfield_XXX" name
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.

Screenshot 2024-02-12 at 14.53.41.png

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

Regards,

Kristian

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-cloud

jira-server