Time estimate fields are showing values in seconds in the exported csv/excel instead of w d h m format
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When a list of Jira issues is exported from issue navigator, all the time estimate fields are exported with values in seconds instead of the values we see in Jira UI.
For example, if the Remaining Estimate field is shown as 4w 2d 3h 55m in the issue navigator, the exported value in CSV would be 647700 (in seconds).
Cause
This works as designed and we have documented it as known behaviour. Please refer JRASERVER-26580: Issue Navigator Excel export change time to seconds format for more information.
Solution
Many a times, it is not desirable to view the values in seconds and seeing it in the same way as shown in the issue navigator makes more sense. Since the bug fix request is not yet resolved, users can make use of the formula in excel to convert those values in the desired format (w d h m format).
Before devising the formula, we should understand the below points with respect to Jira:
1 week=5days
1 day=8 hours
1 hour=60 minutes
Consider the cell in which the value is stored is AF2. AF is the column and 2 is the row. One can use the below formula in Excel to convert the seconds into the format shown in the Jira issue view.
1
=INT(AF2/144000) & "w " & INT(MOD(AF2,144000)/28800) & "d " & INT(MOD(MOD(AF2,144000), 28800)/3600) & "h " & INT(MOD(MOD(MOD(AF2,144000),28800),3600)/60) & "m"Refer to the below screenshot from one of the sample issue. It shows issue KANBAN-2 with Remaining and Logged time.
Now, when the user exported this as CSV/excel, we can see it as below. Seconds are highlighted in the red box and the converted values can be seen in the blue box:
Was this helpful?