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

How do I convert Jira structure "Time in status" to hours or a number?

Elaine Beckford

Currently the Time in status structure column outputs strings such as

8h 24m

1d 15h 39m

1w 6d

I need to see number of hours a ticket has been in a particular state or group of states.

The structure is exported to excel for analysis using pivot tables

Accepted answers

All comments

Marc -Devoteam-

Hi @Elaine Beckford

You would need a custom field, based on number type and then using automation rule and smart values store the number of hours into this field.

Jason Krewson

If you're on Structure Cloud (as you mention "Jira structure"), the Time in Status column has a Style format setting that can display durations directly as Hours (for example, 95.38h) instead of 1w 6d, making exports much easier to analyze in Excel.

Steps

Open the Structure.
Locate the Time in Status column.
Click the column header menu (or column settings/configuration).
Select Edit Column.
Find Style format.
Change it from Basic to Hours.
Save the column.

Elaine Beckford

Thanks Jason, I don't see the Style format option. Please share a screen shot. Thanks Structure Time in status.jpg

Rahul_RVS

Hi @Elaine Beckford

For detailed reporting for cycle times / time in status for your issues, if you would like to try out an alternate mktplace app, pls explore

Time in Status Reports

With this app you generate time in each workflow status for multiple issues with multiple filter and grouping options. It works for current issues and closed ones as well.

The report below shows the cycle time of the issues. This can be achieved by the status grouping feature of the app, wherein you can define your own cycle/resolution times by grouping up specific statuses.

The calendar setting feature allows to define your working days and times. The app will calculate time in status based on this calendar only.

The app can be easily added as a dashboard gadget and can be shared with colleagues.

More details here.

Disclaimer : I am part of the app team for this add-on

Elaine Beckford

Thanks Marc. I will investigate and try it.

Elaine Beckford

Thanks for this suggestion Rahul. Unfortunately we do not currently subscribe to this add-on.

Stepan Kholodov

Hello @Elaine Beckford

As @Jason Krewson mentioned earlier, it is possible to change the Style of the column but it's not available in your case: it looks like you're on Jira Data Center, and this option is only present in Structure on Jira Cloud.

You would need to use the Formula column to convert the output of the Time in Status column. You can try this formula: calendar_hours(timeinstatus)
It will convert the values into hours in the 24/7 format, e.g. 1w 6d will become 312 (hours)

Please note - by default, the timeinstatus variable will be highlighted in red in the Formula's configuration, so you would need to map it correctly: click on it, and select the Time in Status column on the list.

I hope this helps. If you have more questions about Structure, please reach out to us directly at our support portal.

Best regards,
Stepan
Tempo (the Structure app vendor)

Jason Krewson

The steps I mentioned were for Structure Cloud, you might not have that app or you might be have Data Center. The post is tagged as cloud.

Petru Simion [Simitech Ltd.]

Hi @Elaine Beckford,

If you're open to considering a Marketplace alternative, you may want to take a look at Time in Status Reporter for Jira.

Disclosure: I'm part of the team that developed this app.

One of the features that may help with your use case is that you can choose the time format used in the report. For example, instead of displaying values like:

1w 6d
1d 15h 39m
8h 24m

you can display the results directly as Minutes, Hours, Days, or several combined formats (Days:Hours:Minutes, Hours:Minutes, etc.), making the exported data much easier to analyze in Excel.

The app also supports two presentation modes:

Columns – each status (or status group) is shown as its own column, which is convenient for Pivot Tables and comparisons.
Rows – each issue/status combination is displayed on its own row, which many BI tools and Excel users prefer for filtering and aggregations.

Here are a couple of examples.

Columns view

Rows view

Another capability that many teams find useful is Status Groups.

You can create your own groups (for example, Cycle Time, Lead Time, Development Time, Waiting for Customer, etc.) and save them either as:

Global settings (shared with all users), or
User settings (private to your account).

In addition, each issue type can have its own definition of a status group. For example, a Story can calculate Cycle Time using one set of statuses, while a Task or Bug can use a completely different combination. This provides much more flexibility than assuming every issue type follows the same workflow.

For example:

Finally, the app supports working calendars, which can also be saved as Global or User calendars. Each calendar lets you configure:

Working hours independently for each day of the week
Multiple working periods per day (for example, 08:00–12:00 and 13:00–17:00)
Custom holidays
Time zones

This allows Time in Status calculations to reflect your actual business calendar instead of assuming a 24×7 schedule.

You can find more details here: Time in Status Reporter for Jira

Hopefully this gives you another option if you frequently export time-in-status data for Excel analysis.

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