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; } } }

Human readable "[CHART] Time in Status" in Excel export ?

Guy

(on cloud for my case)

The custom field "[CHART] Time in Status" when exporting all issues to Excel shows as

3_*:*_1_*:*_0_*|*_10003_*:*_1_*:*_40208

Is there an out-of-the-box way to make it a bit more friendly, by changing the status ID to status name, and the number to d/h/m...?

There are plenty great plugins to leverage this field, but they are third parties (security concern) and mostly not free.

Accepted answers

Bill Sheboy

Hi @Guy

First thing, I have never used that internal, custom field before. With that out of the way...

Based on what you show, and a quick experiment, I hypothesize you could use spreadsheet formulas (or macro code) to:

split the values on the colon : delimiters
use a lookup table to map the Status Name to their Status ID
divide the apparent time in seconds for your desired unit of measure

One challenge is the field contains zero-to-many elements for the transitions, and so whatever method is used will need to handle that. Thus, a macro may be better for each measure.

Kind regards,
Bill

Iryna Menzheha_SaaSJet

Hi @Guy 👋

Jira’s custom field logic is built more for UI display and reporting inside Jira. The exported raw data is designed for internal processing, not for easy interpretation outside the system.

This format uses:

Status IDs instead of names
Time values in seconds
Delimiters (_*:*_ and _*|*_) to separate entries

Unfortunately, Jira Cloud doesn’t offer an out-of-the-box way to convert this data into a human-readable format (status names + time in d/h/m) when exporting to Excel.

Workaround Options:

1. Manually Convert in Excel (Advanced Users)

You could:

Use Jira’s REST API to map status IDs to names
Convert the time values (in seconds) into d/h/m format using Excel formulas
Parse the raw string using TEXTSPLIT, SUBSTITUTE, and VLOOKUP (if you have a reference sheet of status IDs)

2. Alternative: Use "Time in Status" by SaaSJet

Since you're looking for a more readable export without writing scripts or API calls, the Time in Status might still be worth exploring.

It provides:

Human-readable Excel exports (Status Name + Duration in h/m)
Visual reports and pivot tables
Supports Jira Cloud and doesn’t require advanced configuration
GDPR-compliant and supports Forge-based data processing for enhanced security

Frame 1021.png

It works retroactively, so you can analyze historical issues too.

Let me know if you’d like a walkthrough or have specific use cases in mind!
Add-on developed by my team.

All comments

Bill Sheboy

Hi @Guy

First thing, I have never used that internal, custom field before. With that out of the way...

Based on what you show, and a quick experiment, I hypothesize you could use spreadsheet formulas (or macro code) to:

split the values on the colon : delimiters
use a lookup table to map the Status Name to their Status ID
divide the apparent time in seconds for your desired unit of measure

One challenge is the field contains zero-to-many elements for the transitions, and so whatever method is used will need to handle that. Thus, a macro may be better for each measure.

Kind regards,
Bill

Guy

My own analysis for people interested:

3_*:*_2_*:*_185775_*|*_10004_*:*_1_*:*_154135_*|*_10003_*:*_2_*:*_70336

Status separator: |
First nb: statusID, for example ID 3 = 'In Progress'. List of IDs can be found in the admin 'Statuses' page
Second nb: nb of times the issue was in. It does not count the time for the current issue such as 'Done'
Third nb: cumulated duration in milliseconds that status was in

This field seems to be updated when the resolution is set, typically in the transition 'Post Functions'.

Guy

Indeed it is possible, my macro would have to know all statuses IDs. Not easy to maintain...

Have you been able to leverage (graph, table, stats, etc.) easily this field type without commercial plugin?

Bill Sheboy

No, I have not used this field before and so not tried to map it to a chart. Some other things to consider...

When any team-managed projects are involved, their status values are not all available from the site admin pages. Instead, I recommend using the REST API for the specific project to get all of the possible status ID / Name values for the issue types in the project.
Next, please note this data is stored one time for a single work item, only containing the data for the statuses to which the work item transitioned. However the dashboard gadget shows these values over time, using the changelog entries to build a table of values to plot. And thus: this custom field could only be used to show the sum and not a time-series chart.
Finally, my experiments showed this custom field was only updated when the dashboard gadget was drawn. The values do not appear to be regularly updated for all other work items in all projects of the Jira site.

Depending upon what you are trying to visualize / chart, you may want to use a standalone script (e.g., a separate program) to parse the Jira CSV export to interpret the data, create new columns in the CSV, and then open that in a spreadsheet.

But if you need an actual time-series chart, that will require reading and parsing the changelog (with the REST API) for each work item to create the plottable data.

Aron Gombas _Midori_

@Guy I understand your concerns about using a third party app, but if there is no "secure" solution, you can definitely solve this by adding a custom calculated field to the Better Excel Exporter app.

It should be fairly straightforward, especially because you already reverse-engineered the field value. To get the status name by the status ID, you can use this REST API endpoint.

Danut M _StonikByte_

Hi @Guy,

Most likely it is not possible to get a more friendly display of that field value during export.

You could eventually replace those status IDs in the exported Excel file by obtaining their corresponding names via this REST API endpoint (instead of using Jira admin): https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-status/#api-group-status. But this is not so simple and error-prone.

But what is your end goal here, and what do you want to obtain in the end? A chart, a list with time in status for each issue?

As you mentioned, there are app on Atlassian Marketplace that offers all this type of statistics. My advice is to try some of them.

In case you prefer using an app, our Great Gadgets app has a Time in Status gadget that allows generating all type of charts and also lets you display and export in CSV data about individual issues. And it offers additional options, like for example excluding weekends, which Jira does not offer.

The app is secure and our company is certified in ISO 27k security management standard.

Danut.

Iryna Menzheha_SaaSJet

Hi @Guy 👋

Jira’s custom field logic is built more for UI display and reporting inside Jira. The exported raw data is designed for internal processing, not for easy interpretation outside the system.

This format uses:

Status IDs instead of names
Time values in seconds
Delimiters (_*:*_ and _*|*_) to separate entries

Unfortunately, Jira Cloud doesn’t offer an out-of-the-box way to convert this data into a human-readable format (status names + time in d/h/m) when exporting to Excel.

Workaround Options:

1. Manually Convert in Excel (Advanced Users)

You could:

Use Jira’s REST API to map status IDs to names
Convert the time values (in seconds) into d/h/m format using Excel formulas
Parse the raw string using TEXTSPLIT, SUBSTITUTE, and VLOOKUP (if you have a reference sheet of status IDs)

2. Alternative: Use "Time in Status" by SaaSJet

Since you're looking for a more readable export without writing scripts or API calls, the Time in Status might still be worth exploring.

It provides:

Human-readable Excel exports (Status Name + Duration in h/m)
Visual reports and pivot tables
Supports Jira Cloud and doesn’t require advanced configuration
GDPR-compliant and supports Forge-based data processing for enhanced security

Frame 1021.png

It works retroactively, so you can analyze historical issues too.

Let me know if you’d like a walkthrough or have specific use cases in mind!
Add-on developed by my team.

Guy

Excel macro here for people who are interested:

https://github.com/guyplusplus/Jira-TimeInStatus-Excel-parser

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-cloud