How can I compare dates between an issue and a sprint to run an automation?

[Bruno Leite Cervantes]

Hi there,

I'm trying to create an automation to add a label in a card added after a sprint starts. In my organization, we have to label the new cards added after the sprint started as "added points". To avoid doing this manually - most of the times people forget to add the label - I created an automation to compare the issue created date with the sprint start date. I tried to use smart values to save each of dates in the same format, and I used {{issue.created.format("dd/MM/yyyy")}} and {{issue.Sprint.startDate.format("dd/MM/yyyy")}} to assign the format dates to the two smart values created. When I run the automation, I'm getting an error, so I added a last step to write the two dates (issue.created and issue.Sprint.startDate) in the comment section.  When it ran again I got something like this:

- issue.created = 2024/07/09 12:30:22.22 pm" 
- issue.Sprint.startDate = Wed, Jun 20, 2024 1:30:05 pm"

I guess the format is not working due to the different format the dates are set in the issue and the sprint. Is there a way to compare these dates? Thank you. I appreciate your help.

Regards,
Bruno

[Laurie Sciutti · Accepted solution]

hello @Bruno Leite Cervantes and welcome to the Community!  If you happen to use ScriptRunner, you might be able to use the addedAfterSprintStart Enhanced Search JQL function:  https://docs.adaptavist.com/sr4jc/latest/features/scriptrunner-enhanced-search/jql-functions#addedaftersprintstart 

[Mikael Sandberg · Accepted solution]

Hi @Bruno Leite Cervantes

Welcome to Atlassian Community!

Have you tried using one if the other formats to compare the dates instead of using the format? Like jiraDate or jqlDate?

[Bruno Leite Cervantes]

Hi @Mikael Sandberg ! Yup, I did and still doesn't work. Here's the screenshot with the issue created date and the sprint start date. Even if I use jiraDate, the "diff" function isn't working when I try to compare the dates.

 

[Mikael Sandberg]

One thing that you could try is convert the sprint start date, which looks like it is a text converted date, to a date format like this:

 {{issue.Sprint.startDate.toDate}}

[Bruno Leite Cervantes]

I made a workaround to manipulate the dates. I'm running some tests, but apparently is working. Thank you for your tips!

Regards,

Bruno

[Bruno Leite Cervantes]

What I did was: 

1. Check if {{issue.created.diff(now).days}} is greater than {{issue.Sprint.startDate.diff(now).days}}. If it is, I'm labelling the card as "pull-ahead".

2. Check if {{issue.created.diff(now).days}} is less than {{issue.Sprint.startDate.diff(now).days}}, I'm labelling as "new point".

[Trudy Claspill · Accepted solution]

Hello @Bruno Leite Cervantes 

Welcome to the Atlassian community.

When asking for help with an Automation Rule, it is vital that you share with us screen images showing the complete rule and the details of each step. Obscure confidential information. It is also helpful if you share with us the details in the Audit Log generated when the rule ran.

[Bruno Leite Cervantes]

Thanks for the advice @Trudy Claspill . Here are the screenshots. The last screenshot is the date written in the comment field after the automation ran.

[Trudy Claspill]

Why are you creating variables and then using the variables in your comparison? You can simply use the smart values you used to create the variables.

{{issue.created.diff(issue.Sprint.startDate).days}}

When you create Variables the values are stored as text. The "diff" function can be used only with smart values that are Date or Date/Time types.

I think if you use the Log action to print out the smart value you were using with the Variables you will find you are not getting the answer you expect. But if you simply use the original issue smart values then you will get closer.

The diff function can return a positive or negative number. I recall that depends on how the date fields are referenced, but I never remember which way to go. So you might want to use a Log action to print out the above value also to check if it is returning a positive or negative number as you expect.

[Mate Moldovan]

I went on a journey in the date formatting documentation, and found the below smart value working perfectly for me to convert Sprint dates (that look like this: Sprint End Date is Fri Jan 17 22:00:00 GMT 2025) into our Jira project date format:

{{issue.Sprint.endDate.format("yyyy-MM-dd")}}