Bulk change automation to calculate lead time

Question

Hi everyone,

I now tried 2h of ChatGPT to solve my issue but the KI is miserably failing. Now I´m hoping for human intelligence to help me further 😄

Setup

Jira Cloud working on a team-managed project.

Custom Fields

1. Lead Time Start: Date field which is getting populated automatically with the current date once the issue is transitioned to a certain status.

2. Final Lead Time: Number Field that should be calculated based on when the issue transitioned to "Done" state

The task

I already have an automation, that calculates the Final Lead Time once issues get transferred to DONE (this is working fine). However, I still have a whole bunch of tickets, that went to "Done" before this automation existed. Still they have the "Lead Time Start" date existing.

I know want to create an automation to run once, that calculates the days between the "Lead Time Start" date, and the date the issue transitioned to done and populate the number to the "Final Lead Time" field.

Hint: The "issue.statuscategorychangedate" operation doesn´t seem to work in this context - anybody has a clue how to achieve this?

Bill Sheboy · Answer

For more context, please show:

* an image of your complete rule in one continuous image for continuity
* an image of the rule action and smart value expression that is not working as expected
* an image of the audit log details for the rule execution, with all the areas at the right side expanded

Thanks!

Ronny_matthies · Answer

Hi Bill, thanks much for the quick response. Unfortunately the "toDate" function is not working for team managed projects - at least not in automations...

Bill Sheboy · Answer

Hi @Ronny_matthies -- Welcome to the Atlassian Community!

First, I recommend not using bot tools (even the built-in one from Atlassian) unless you carefully review and understand the rule logic.  Such "guessers" cannot access the configuration of your specific products, site, work item types, etc., and so often provide unhelpful guesses based on generic concepts from other languages.

Next, the statuscategorychangedate is a text version of the date / time for the most recent status category change.  From what you describe, this would always be when the work item entered a "Done" status, and thus likely the end point for your time range.

Back to your question, perhaps try this:

* Write some JQL to identify the work items to update, where the number field is empty and the starting date / time one is not.  Test that with example searches.
* Create a rule with a Scheduled trigger, using that JQL, to perform the updates, allowing the rule to run every 15 minutes, until all caught up, and then disable it.
* For the field update, use the date / time functions to perform the calculation.  Remember the statuscategorychangedate is text, and so must be converted with toDate for use in the diff().

Kind regards,
Bill