Export issue to Excel

[Szymon Ptak]

Hi, I am trying to export issues to Excel.

I use these options as seen on the picture :

Option 1: generates fields that aren't accurate with weird values.

Option 2: Looks good but I would like the date to be without hours : "dd/mm/yyyy" and the value of the Time spent is in seconds (1800) and should be in minutes (30m).

The time in Excel is displayed in seconds, but in jira it is displayed in minutes (30m) as it should be.

Any way to configure that? Thank you for any informations :)

[Bill Sheboy · Accepted solution]

Hi @Szymon Ptak 

I believe you are seeing the differences between the Jira source data (e.g. 1800 seconds) and the presentation in the user interface/experience views (e.g. 30 minutes).

Once you have the source data in the spreadsheet, have you tried to manipulate it to your format / reporting needs?  Such as converting the seconds to minutes or truncating the hours from the date/time values.

Best regards,

Bill

[Szymon Ptak]

How do I get to the source data ? I am in the filters section.

I can use smart values but I dont know how to get to the 'source data' to be able to manipulate those values.

[Szymon Ptak]

Tried customizing Global settings ( java.date.picker), Look and Feel and I even tried parsing my date via automation (only {{now.jiraDate}} works, any other format gives me Error parsing date string.

[Bill Sheboy]

Hi @Szymon Ptak 

I meant the source data you have downloaded into the spreadsheet.  Please look at how it was exported and use spreadsheet functions to convert to the format you need.

[Szymon Ptak]

Is there any other way instead of plugin?

[Bill Sheboy]

Do you mean the Atlassian built plug-ins for Excel and Google Sheets?

If so, there are several alternatives which do not require purchasing other marketplace add-ons:

• Just export the issues after using a JQL filter search
• Use the REST API to pull data out of Jira.  There are many different REST API methods so you will need to research to find out what may help.
• Use a webhook to push data out of Jira for consumption with a log file parser that can handle JSON

[Szymon Ptak]

I just want to configurate the data that I will be getting when i click : Export to Excel. 

Just need to change dates and format seconds to minutes or something. I don't want to always use excel formulas when doing the reports. Thanks

[Bill Sheboy]

In general, that is not possible.  You may change your localization settings for date/time but not the export formats for specific fields.

You note not wanting to use Excel formulas when doing the reports.  If you use the free Atlassian plug-ins for Excel or Google Sheets, you can pull the data into one tab and process it on another, reducing the needs to modify the spreadsheet forumulas with each download.

Please look here for information about the Excel version: 

https://marketplace.atlassian.com/apps/1221301/jira-cloud-for-excel-official?hosting=cloud&tab=overview

If that solution will not work, please investigate the marketplace for reporting add-ons for purchase.

[Aron Gombas _Midori_]

I don't think you can configure the output format using the built-in export feature.

As an alternative, Better Excel Exporter allows configuring both. Defaults for those settings:

1. "Time spent" is exported in hours, the most sensible default. But you can turn that to minutes by modifying the formula which turns milliseconds to hours to convert to minutes (see the "field-helper-tool.groovy" script).
2. All date time values as exported with minute precision, but it can be changed to any format (also see the same script).

(Discl. My team is working on this paid and supported app, so you are welcome to ask for more help here.)

[Rina Nir]

Hi @Szymon Ptak 

If you basically needed an external static copy of Jira data then you could use the App Jira Snapshots for Confluence -> it creates static copies of Jira data in Confluence. Advantage is that if you take a Snapshot today, and another one tomorrow- you can easily compare between these two Snapshots. Its handy and helps you quickly see what changed in Jira.

Sure it has also other advantages like the fact that you can make multilevel reports (like traceability reports). 

It does seem to display durations and dates in a more friendly manner then what you experience with the excel export

Disclosure: I am the CEO of RadBee, the maker of this App