Custom field: Calculating days between creation and issue moved date

[Mitesh Chauhan]

Hi all,

I have a project where tickets will be moved from other projects. I would like to create a report in my project.

What I want to record or calculate for each ticket is:

When a ticket is move to my project - 

1. No of business days = Ticket Moved date - Ticket created date 

When I resolve a ticket

1. No of business days = Ticket Resolved date - Ticket Moved date 

I would like to apply this solution to all the tickets were retrospectively moved. Can someone guide me through the path to reach this?

Many thanks in advance

Mitesh

[John Funk]

Hi Mitesh,

Sure - You will need to create two new custom Number fields to store the different calculations. Make sure you name them something different from each other. 

Then create an automation rule to do the calculation of the differences in the dates. The rule will be based on a Field Value changing - like the dates used in the calculations. 

Then you have a new action for Edit Issue. Choose the number field(s) you want to update. Then you will use a formula to do the difference. Something like:

{{issue.customfield_16825.diff(now).days}}

You can use the custom field ID or the field name

like: {{issue.Ticket Moved Date.diff(now).days}}

Here is an example:

[Bill Sheboy]

Hi @Mitesh Chauhan 

Adding to John's answer:

This approach will help for any future moved issues, by triggering on Issue Moved and checking for a change of the project.

This will not find any issues moved before this rule is put into place.  If you need to do that, you would need to either:

• set your custom fields manually by reading the history, or
• call the REST API from the rule to get the move date/time from the issue history.

Kind regards,
Bill

[Mitesh Chauhan]

Thanks @John Funk and @Bill Sheboy  almost got it working - except a minor issue.

To update all the issue prior to this rule - I have gone down the route of importing through CSV based on Issue key.

What i get is:

02/Sep/22 14:30 in CSV ends up as 31 Aug 2022, 05:34 in Jira when I use dd/MMM/yyyy hh:mm format which makes no sense!

What am I missing here?

[John Funk]

What field are you updating? 

[Mitesh Chauhan]

Thanks  @John Funk 

I am updating Move To Project Date for all the prior issues before I applied this rule. 

In my CSV all my date is in 02/Sep/22 02:30 PM format and when I imported for one ticket, date got imported correctly but not the time.

What should be the format of time?

[John Funk]

What was the value for the time that got imported? 

[Mitesh Chauhan]

Value in CSV: 16/Sep/22 13:32 and

Format used during Import: dd/MMM/yy hh:mm

Mapped value before Import:

Value that got imported:

[John Funk]

This sounds like it might be an issue with the time zone attached to your Jira instance. I recommend contacting Atlassian Support to see if they can pinpoint the problem. 

https://support.atlassian.com/contact/#/

[Mitesh Chauhan]

Thanks @John Funk - ticket opened with Atlassian Support.