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 

0m

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

[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.

[Stepan]

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)

[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 for this suggestion Rahul. Unfortunately we do not currently subscribe to this add-on.

[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

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

[Elaine Beckford]

Thanks Jason, I don't see the Style format option.   Please share a screen shot.  Thanks

[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.

[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.

See also this comunity post: https://community.atlassian.com/forums/Automation-questions/Jira-Automation-Convert-Time-Tracking-into-number-in-custom/qaq-p/1650628 

[Elaine Beckford]

Thanks Marc.  I will investigate and try it.