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

[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

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