Community Announcements have moved! To stay up to date, please join the new Community Announcements group today. Learn more
×I have exported a jira filtered issues into excel, however the time spent field shows differently in excel compared to the what we see in jira.
How can i convert the excel sheet timespent column into the same format as in jira?
@Sreedevi Raman Pisharody The values are exported as seconds, by default. Those are very hard to read, I agree.
Although the Jira display format is easy to read, you can't make calculations on those textual values in Excel. It is not trivial to add "3d 4h" to "2h 30m", it is not trivial to compare them, etc.
Therefore, we decided to export time values as (decimal) hours, like "2.5" in Better Excel Exporter. It is both easy to read, unless you have very large values, and can be calculated on.
See column H in this example export:
You may want to consider this.
(Discl. it is a paid and supported app developed by our team. Free for 10 users!)
You seem to be asking a question about Microsoft Excel, and you posted this question in the Jira community. That Jira field is "pretty printing" a number of seconds as a text value, such as "3d, 4h".
I suspect the answer depends upon your specific version of Excel and any addins you have. I recommend checking the Microsoft product documentation and performing a web search to check for options, such as using formulas and division to repeatedly extract the various time elements.
Kind regards,
Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Bill, i completely understand the question is related to excel. I have tried various formulas, related to get the value converted to jira format, but no luck. I am unable to identify whether the value which has been exported from jira is it in secs or minutes.
Is there a way i could figure it out to notice that the time spent column value is in which format getting exported from jira?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The export value from Jira is seconds. And so to convert that, assume the days are 8 hours long, and progressively divide and truncate the value to get the pieces.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Additionally, if Jira shows more than 5 days, it converts it to weeks. These settings/assumptions that Jira takes are configured under Administration > Issues > Time Tracking. The only thing that will not be configurable is how the data is stored. This will always be the amount of seconds.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.