I use JIRA 4.4.1. When I export Issues to excel, the Time Tracking ( Estimate, Original) is in Seconds.
I need it to be in Hours or in day ( The way I entered in JIRA).
Any work around ?
Thanks
Vishnukumar Vasudevan
There is a workaround.
Added a script to WEB-INF/classes/templates/jira/issue/field/duration-columnview.vm file. Please see the script below. It requires an application restart.
#if ($displayParams.excel_view && $duration) #set ($seconds = $textutils.parseInt($duration)) #set ($minutes = $seconds / 60) #if ($minutes >= 60 ) #set ($hours = $minutes / 60) #set ($minutesremainder = $minutes % 60 ) #if (!$minutesremainder == 0 ) #set ($minutesremainder = $minutesremainder * 100) #set ($minutesremainder = $minutesremainder / 60) #end #else #set ($hours = 0) #set ($minutesremainder = $minutes * 100) #set ($minutesremainder = $minutesremainder / 60) #end $textutils.htmlEncode("${hours}.${minutesremainder}")#else $textutils.htmlEncode($duration)#end
This worked well.
Agree, a bit irritating stuff, seconds is too high a granularity. The obvious workaround we used was to have a very small macro in xls to convert the values to hours for the estimate columns.
After changing the code in JIRA 5.1 it shows values in minutes ignoring completely hours, which is very strange because it should show either hours and minutes or default seconds.
I couldn't try in 5.x yet.
From your comment, the scrip works but the calculation goes wrong. Could you cross the script for any typos ?
Also, hope you have restarted the application.
Thanks, Vishnu.
I am using this code on our instance of Jira, and it's giving us some strange results. We have one issue with 6.5 hours, which displays fine in the issue navigator.
When I export it to excel though, it becomes 6.3 hours... Do you have any ideas as to why this would be the case? Another one is 7.25, that becomes 7.15.. Another 2.5 becomes 2.3.
There are many that are strange like that, I cannot understand what would cause this to happen to some but not others.
I am using 5.0.6
Which version of JIRA you use ?
This is a working version for Jira 5.x.x
WEB-INF/classes/templates/jira/issue/field/duration-columnview.vm
#if ($displayParams.excel_view && $duration) #set ($seconds = $textutils.parseInt($duration)) #set ($minutes = $seconds / 60) #if ($minutes >= 60) #set ($hours = $minutes / 60) #set ($remainder = $minutes % 60) #if ($remainder > 0) #set ($remainder = $remainder * 100) #set ($remainder = $remainder / 60) #end #else #set ($hours = 0) #set ($remainder = $minutes * 100) #set ($remainder = $remainder / 60) #end $textutils.htmlEncode("${hours}.${remainder}") #else $textutils.htmlEncode($duration) #end
The above works fine to convert from seconds to hours. How do I convert to show time in 'days'?
Not a workaround as in something you can do in JIRA but if all you want to do is to convert the number of seconds from the export into hours or days in Excel, it just needs a simple Excel formula. E.g. if the value shown in JIRA is 1 day, 3 hours, 45 mins, in Excel export this will appear as 40500:
1. to get hours, use formula "=A1/3600" - this gives you 11.25 which taking into account a working day of 7.5 hours is correct (A1 being the cell containing your secs value)
2. to get days, use formula "=A1/3600/7.5" - this gives you 1.5 which is accurate enough (A1 being the cell containing your secs value, and replace "7.5" with whatever length your working day is)
Note: 3600 being 60 x 60, i.e. 60 secs in a min x 60 mins in an hour.
Great that there's a script...
Does it still work with the latest JIRA 6.xxx ?
Will I have to reapply this script every time I update JIRA ?
*Bump* - we are using Jira 6.x but it doesn't seem to fix the issue. Can anyone confirm the above script (THANK YOU FOR WRITING BTW) works in Jira 6?
ok great - thanks for hitting me back. We'll go back to the drawing board and see if we haven't dotted an i or crossed a t. Thanks!
For JIRA 6.3.12 solution with template (WEB-INF/classes/templates/jira/issue/field/duration-columnview.vm) editing works great. I just have to ajust a bit so that decimal part under 10 minutes is displayed correctly.
#if ($displayParams.excel_view && $duration) #set ($seconds = $textutils.parseInt($duration)) #set ($minutes = $seconds / 60) #if ($minutes >= 60) #set ($hours = $minutes / 60) #set ($remainder = $minutes % 60) #if ($remainder > 0) #set ($remainder = $remainder * 100) #set ($remainder = $remainder / 60) #end #else #set ($hours = 0) #set ($remainder = $minutes * 100) #set ($remainder = $remainder / 60) #end #if( $remainder < 10 ) $textutils.htmlEncode("${hours}.0${remainder}") #else $textutils.htmlEncode("${hours}.${remainder}") #end #else $textutils.htmlEncode($duration) #end
Simple and helpful, thank you.
Hello,
Is there a similar fix for the CSV export introduced in Jira v7+?
The above script only works for Excel export which was part of Jira versions prior to version 7.
(I know we can re-enable Excel export for v7, but I wanted to have the light-weight CSV to have this functionality)
I am interested too for v7+.
It looks like you're new here. Sign in or register to get started.
