Jira Cloud for Google Sheets Add-ons Days in Status field pattern and returned value for duration

David Leal
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 29, 2021

In the post: Parse Time in Status JQL for Days in Status field than can be retrieved from Jira Cloud for Google Sheets Add-ons. The pattern of this field should be something like this:

<STATUS_ID>_*:*_<NUMBER_OF_TIMES_ISSUE_WAS_IN_THIS_STATUS>_*:*_<SECONDS>_*

for each status visited, and delimited by pipeline delimitated (|). I tested it and it seems to provide information according to this pattern (I haven't seen it documented in  the referred Add-ons.

My question is about the duration in <SECONDS> according to this pattern. I am getting information that doesn't match with the information I am getting from my issues in Jira. For example this is the string I am getting for one of my issues:

10060_*:*_2_*:*_9285134986_*|*_3_*:*_2_*:*_18833277913_*|*_10000_*:*_4_*:*_7402051825_*|*_10088_*:*_1_*:*_6073676_*|*_10087_*:*_1_*:*_10970_*|*_10001_*:*_1_*:*_0

In my case the Status ID are mapped as follow:

Status IDStatus
10060Backlog
10000To Do
3In Progress
10036On Hold/Blocked
10087In QA
10088Completed
10001Done

For the following Statuses I get the following seconds:

  • IN PROGRESS: 18833277913
  • COMPLETED:  6073676

Converting this information into days (1 day has 86400 seconds), It would be represent in days the following duration:

  • IN PROGRESS: 217977.7536
  • COMPLETED: 70.29717593

which doesn't look correct according to my issue, here is the information from Jira from the Transition information:

2021-06-29_13-46-10.jpg

From this information, we have:

  • IN PROGRESS: 7 months, which represents 214 days
  • COMPLETED: 2 hours, 0.08 days

which is different from what I am getting from the field Days in Status. As you can see the number of times the issue was in status is correct, but certainly the duration I am not able to match it with my data.

Thanks in advance for any help. If you know how to contact the team support for this Add-ons I appreciate it.

David

 

 

 

 

 

1 answer

1 accepted

0 votes
Answer accepted
David Leal
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 8, 2021

I verified that the information is not reported in SECONDS, it is reported in MILI-SECONDS instead.

Suggest an answer

Log in or Sign up to answer