Original estimate value changed when exported

[Angela Kent-Ashton]

I not sure why the Jira value was changed for every issue exported to a csv file. It looks like it is the same factor applied to each row in file

[Ollie Guan]

Hi @Angela Kent-Ashton,

What changes have taken place, can you use the screenshot to explain?
The Original estimate value export displays by default in seconds.

[Angela Kent-Ashton]

Sorry not on laptop so I can’t screen shot.

in Jira the field has 4 but when I export The row out to csv it is changed to 1440 ? Hi 

it looks like all the rows had the same factor applied.

0.002777777777778 Is the factor

[Ollie Guan]

4 = 4hour? 4 day? 4Week？

[Angela Kent-Ashton]

Hour 

[Jack Brickey]

If the value is 4h in Jira the export should be 14400 (sec).

[Angela Kent-Ashton]

Thanks guys, is the only way to correct is after export to excel?

[Jack Brickey]

@Angela Kent-Ashton,

If in the example the CSV value is 14400 then you should not have to correct it as it is correct. The point is that when you export to csv any of the time fields will be in seconds. Jira stores all time in seconds but transposes them in the display and allows you to choose the unit (h/m/s/d/w). If you wish the CSV to display them in some unit other than seconds then you will want to simply use the excel function/equation.

Now if indeed your earlier comment that Jira shows 4h and the export shows 1440 (not 14400) then there is something wrong for sure but I suspect that is not the case here.

[Ollie Guan]

@Angela Kent-Ashton,

4horu * 3600=14400 seconds,I think @Jack Brickey is right.

There was a post in the community that discussed how to adjust the estimated time export format. Please refer to it

https://community.atlassian.com/t5/Jira-questions/Export-Estimates-in-hours-Day/qaq-p/58647

[Rob Horan]

How is export in seconds useful to the end-user?  (the people paying the bills)

Nobody is interested in the raw data in the DB - they need useful information for reporting.  Jira doesn't even take seconds as an input.

[Jack Brickey]

@Rob Horan , maybe reach out to Atlassian via Feedback.

[Rob Horan]

@Jack Brickey Have you seen how effective that is?  LIke the desired ability to rename groups, nearly 20 years old :)

https://jira.atlassian.com/browse/JRASERVER-1391

[Jack Brickey]

Indeed their are some old ones out there but there are a lot that are resolved too in my experience. Regardless I do find the feedback channel is more effective than old community posts as they are not actively monitored by Atlassian PMs.

[Rob Horan]

OK - let me see if I understand this correctly. The only way to export time information and get it into a readable form is to:

1. Export your data to CSV.
2. Import that CSV file into a spreadsheet tool, such as Excel, Numbers, or Google Sheets.
3. Insert columns and create formulas that convert seconds to readable time units in the new columns.

Therefore, to extract values for diagnosing Advanced Roadmaps issues, you could use the following formulas:

• Minutes = seconds/60
• Hours = seconds/3600 OR minutes/60
• Days = seconds/86400 OR minutes/1440 OR Hours/24
• Weeks = seconds/604800 OR Minutes/10080 OR Hours/168

I wrote the formulas assuming that there are five columns, each calculating a value based on the previous column. (getting minutes from seconds, hours from minutes, days from hours, and weeks from days)

But here's the problem - that assumes a 24 hour day and a seven-day week. Isn't Jira operating on a five-day workweek with 8 hours per day?

However, this is not the case, and as far as I can tell, there is no documented process in Atlassian's documentation. 

So then, for a five day week with 8 hours per day, I assume the formulas are:

• Minutes = seconds/60
• Hours = seconds/3600 OR minutes/60
• Days = seconds/28800 OR minutes/480 OR Hours/8
• Weeks = seconds/144000 OR Minutes/2400 OR Hours/40 OR Days/5

BUT, since this might not be the case for everyone, for people to get the data they need, they must first know what their global time tracking settings are. This information is in the Issues section of Jira Administration, which is impossible for non-admins (pretty much every Jira user) to access.

So ultimately:

• There is no documented process for converting seconds to other time units based on Jira's Global time tracking settings
• Without access to Jira's Global time tracking settings, there is no way for non-admins to convert from seconds to a human-readable format reliably.
• Even with the settings, without a process, one must also have the skillset to convert this information into useful information. Don't assume everyone knows how to use Excel. I've had extended conversations with people about making a single-level bulleted list using the bullet list button in Confluence.

[Rob Horan]

@Jack Brickey is the feedback channel separate from support.atlassian.com?