Export time to first response and resolution in minutes

Marcelo Gallego
Contributor
July 23, 2021

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. 

4 answers

1 accepted

1 vote
Answer accepted
Marcelo Gallego
Contributor
August 6, 2021

Hi!

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

That's what I did:

  1. Create a tab "Raw data" using Google Sheet Jira integration. Import fields until column O
  2. 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`=[]\;',./!@#$%^&*()");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!

0 votes
Matt Hutchinson October 26, 2023

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. 

0 votes
Suvradip Paul
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.
July 26, 2021

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
Contributor
July 27, 2021

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.

0 votes
Girish_Shenoy
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 24, 2021
Marcelo Gallego
Contributor
July 26, 2021

Hi!

Unfortunately We're on a cloud version.

Do you know any alternative for cloud?

Regards!

Suggest an answer

Log in or Sign up to answer