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

[David Leal]

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 ID	Status
10060	Backlog
10000	To Do
3	In Progress
10036	On Hold/Blocked
10087	In QA
10088	Completed
10001	Done

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:

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

[David Leal · Accepted solution]

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