nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Export time to first response and resolution in minutes

Marcelo Gallego

Hi!

I'm using Google Sheet integration to export raw data and integrate with our BI (Data Studio).

I have a problem with two fields of service desk:

time to first response
time to resolution

Both are exported as "5h 20m" (text format) but I'd like to export them in number format, e.g. in minutes.

Does anyone a similar issue and, most important, a solution? :=)

Thanks in advance!

PD: I've installed the time to SLQ plugin, but I can't add the information to my Google Sheet downloadable fields.

Accepted answers

Marcelo Gallego

Hi!

Just for helping another person. Finally I solved the problem using Google Sheets formulas.

That's what I did:

Create a tab "Raw data" using Google Sheet Jira integration. Import fields until column O
Create a second tab "Processed data"
- On the A1 cell I put the formula

ARRAYFORMULA('Raw Data'!A1:O2000) 

[Remember: my tab "Raw data" ends on column O)

- Then I create 8 new columns, from P to W. The column headers are

h1 t response | h2 t resp. |  m t resp. | t resp. m | h1 t resol.  | h2 t resol. | m t resol. | t resol. m

On P2 I put this formula

 SI.ERROR(SPLIT(MINUSC(K2);"qwertyuiopasdfghjklzxcvbnm`=[]\;',./!@#$%^&amp;*()");0)

[All formulas are in Spanish version. English version has "," rather than ";" and function names are in English]

On S2 I put this formula

SI(R2<>0;R2*60+P2+Q2;SI(Q2<>0;P2*60+Q2;P2*60))

I did the same on T2 and W2

Finally I got the number of minutes of my time to first responde and time to solve on cells S2 and W2

With this number I've could create my Data Studio report.

Thanks for you answers and regards!

All comments

Girish_Shenoy

You can achieve this using https://marketplace.atlassian.com/apps/1212652/better-excel-exporter-for-jira?hosting=server&tab=overview&utm_source=cac&utm_medium=cac-comment&utm_campaign=gh

Marcelo Gallego

Hi!

Unfortunately We're on a cloud version.

Do you know any alternative for cloud?

Regards!

Suvradip Paul

Hi @Marcelo Gallego ,

Thanks for posting in community.

Could you please search like this in Marketplace - https://marketplace.atlassian.com/search?hosting=cloud&query=Excel%20Report ?

Does this help solving your issue - https://marketplace.atlassian.com/apps/1212073/exporter-export-issues-to-excel-csv-pdf?hosting=cloud&tab=overview ?

Note - I am not part of any of these marketplace products and this response is not part of any paid promotion.

Cheers

Suvradip

Marcelo Gallego

Hi Paul,

thanks for your response, but what I'm looking for is something like "Jira Cloud for Sheets add-on" or just a way to edit the format of some Jira Fields.

I want to keep using Data Studio + Jira Cloud for Sheets because I dominate them and are extremely useful for me.

Thanks again for your reply and regards.

Marcelo Gallego

Hi!

Just for helping another person. Finally I solved the problem using Google Sheets formulas.

That's what I did:

Create a tab "Raw data" using Google Sheet Jira integration. Import fields until column O
Create a second tab "Processed data"
- On the A1 cell I put the formula

ARRAYFORMULA('Raw Data'!A1:O2000) 

[Remember: my tab "Raw data" ends on column O)

- Then I create 8 new columns, from P to W. The column headers are

h1 t response | h2 t resp. |  m t resp. | t resp. m | h1 t resol.  | h2 t resol. | m t resol. | t resol. m

On P2 I put this formula

 SI.ERROR(SPLIT(MINUSC(K2);"qwertyuiopasdfghjklzxcvbnm`=[]\;',./!@#$%^&amp;*()");0)

[All formulas are in Spanish version. English version has "," rather than ";" and function names are in English]

On S2 I put this formula

SI(R2<>0;R2*60+P2+Q2;SI(Q2<>0;P2*60+Q2;P2*60))

I did the same on T2 and W2

Finally I got the number of minutes of my time to first responde and time to solve on cells S2 and W2

With this number I've could create my Data Studio report.

Thanks for you answers and regards!

Matt Hutchinson

I know I'm digging up an old thread, but I hit the same issue recently and used the following to translate the data in Excel. Hopefully it helps someone.

=IF(ISNUMBER(VALUE(A1)), A1, IF(ISNUMBER(SEARCH("h", A1)), LEFT(A1, FIND("h", A1) - 1) * 60, 0) + IF(ISNUMBER(SEARCH("m", A1)), IF(ISNUMBER(SEARCH("h", A1)), MID(A1, FIND(" ", A1) + 1, FIND("m", A1) - FIND(" ", A1) - 1), LEFT(A1, LEN(A1) - 1)), 0))

nb: You'll need to update A1 to be whatever cell you want to translate.

For my reporting I've got the raw data from Jira export on one Excel sheet and then use that formula on another to translate the values.

It's working pretty well for me so far and means the amount of extra col's is limited to the minimum.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-service-desk

jira-cloud

google-sheets

time-to-resolution

time-to-first-response