Date calculation in Jira automation

[Fenner Matheus]

Hello Community!

I'm trying to do a date calculation in a Jira automation for the first time, but I'm not being able to get it right, so I'd like to see if anyone here has already gone through the same and could give me a hand =)

I'm trying to calculate the difference between to dates that are stored in different custom fields, and store the result of the math in a third custom field. I'm using the action "Edit issue fields" and, so far, the expression I came up with is the following:

{{issue.fields.Acceptance date.diff(issue.fields.Lead time start).days}}

The bolded strings in the expression are the custom date fields I'd like to calculate the difference.

[Tim Perrault · Accepted solution]

I wonder if you used the customfield ID would it make a difference? Replace the Xs with the field ID number.

{{issue.fields.customfield_xxxx.diff(issue.fields.customfield_xxxx).days}}

[Fenner Matheus]

Thanks for the help Tim!! Would you be able to help me with where to find the customfield ID?

[Tim Perrault]

If you go to settings --> Issues --> Custom Fields. You can search for the custom field. Once you find the custom field you should see 3 dots, click on this then hover over edit and you will see a URL on the bottom of your browser and at the end of the URL you should see the ID. Something like below:

[Teh Kok How]

I have the same requirement and tried this approach but the value ends up being zero instead of one day. What do I miss?

[Fenner Matheus]

Could you please share here the expression you are using for the calculation, @Teh Kok How ?

[Teh Kok How]

This works for me: `{{issue.Actual start.diff(now).days}}`

[John Funk]

Hi @Fenner Matheus  - Welcome to the Atlassian Community!

Try this: {{issue.Acceptance date.diff(issue.Lead time start).days}}

[Fenner Matheus]

Thanks for helping me out, John!! I have implemented your suggestion and it let's me publish the automation, but it doesn't update the field =(

[John Funk]

Can you share a screenshot of the audit log when you run it?

[Fenner Matheus]

In this automation I also populate a different field with the current date, and this action is working fine...just the date diff that isn't =(

[John Funk]

And what value does it put in the Lead time field? Just nothing? 

Also, but custom fields are the same type of date fields?

[Fenner Matheus]

The suggestion below given by Tim helped me get it to work. Thanks also for your help, John!!

[Surti K]

Team

        My question is similar in line to what Fenner Mateus started with this discussion on. Thanks to him for bringing up such an important topic and Thanks to the team for the wonderful pointers. 

I applied a formula similar to the one listed above ( {{issue.Acceptance date.diff(issue.Lead time start).days}} ) to the Automation Rule.

The 1st formula I applied yielded negative value

Formula was - {{issue.Resolved.diff(issue.Created).days}} 

Wherein the Created and Resolved values were as listed below

Created: 18 Dec 2023 12:10 pm
Resolved: 1/25/2024 3:38 pm

Answer: -38 

Changed the formula to 

{{issue.Created.diff(issue.Resolved).days}}

which yielded a positive value for these dates

Created: 26/Dec/23 10:42 AM
Updated: 08/Jan/24 6:41 PM

However the value returned was off by 1 day is what I think. 

Computer value: 30 days. 

2 Given today is 26th Jan 2024 and we go back to 26th Dec 2023, should'nt the value be 5 days for Dec and 26 Days for Jan to a total of 31 days ?

Just wanted to run by the team for sanity purposes.

Thanks

Surti

[Emre Toptancı _OBSS_]

Hello @Fenner Matheus ,

I know this question is focused on configuring automation rules but it looks like you are trying to calculate a Lead Time metric. I can offer another way that is much easier, more flexible, and more powerful.

Our team at OBSS built Time in Status app for these kinds of reporting. It is available for Jira Server, Cloud, and Data Center. 

Time in Status allows you to see how much time each issue spent on each status and on each assignee. 

       

The app has Consolidated Columns feature. This feature allows you to combine the duration for multiple statuses into a single column and exclude unwanted ones. It is perfect to get measurements like Issue Age, Cycle Time, Lead Time, Resolution Time etc.

It is worth noting here that the app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows or set any automation rules. You can get reports on your past issues as well. It supports both Company Managed and Team Managed projects.

For all numeric report types, you can calculate averages and sums of those durations grouped by the issue fields you select. For example total in-progress time per customer (organization) or average resolution time per week, month, issuetype, request type, etc. The ability to group by parts of dates (year, month, week, day, hour) is particularly useful here since it allows you to compare different time periods or see the trend.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

And the app has a REST API so you can get the reports from Jira UI or via REST.

  

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issue type.)
• Export your data as XLS, XLSX, or CSV.
• Access data via REST API. (for integrations)
• Visualize data with various chart types.
• See Time in Status reports on Jira Dashboard gadgets

https://marketplace.atlassian.com/apps/1211756/

EmreT

[Surti K]

EmreT

        Thank you for the pointer. 

Will look into that.

Thx