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.
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.
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.
Which version of JIRA you use ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I am still in 4.4.x, haven't tried in 5.x. But, plesae find the below comment from Lukasz, they also face some calculation error in 5.x.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
For anyone encountering this problem, this is how I fixed it:
#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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Simple and helpful, thank you.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ?
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
*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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, I can confirm this is working in at least 6.0.2.
Also, yes you will have to update this everytime you update Jira. But Jira will give you a list of changed files, including this one, when you're doing the upgrade.
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.
When I am pulling the Jira's Time Tracking Report, it is still showing estimate in seconds.
I copied the exact above code in the WEB-INF/classes/templates/jira/issue/field/duration-columnview.vm file on my standalone jira.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Tushar, we are using this on 6.2.1 and it's working fine.
What problem are you having? Did you update the correct file? Have you recently upgraded? If so, you'll have to reapply all the changes you made to the file.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Can you paste your file here (in code tags) so I can see if there's a mistake somewhere?
Also, have you restarted Jira since you made the change to the file? And what format are you exporting to? I think this only changes the excel format.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is how my duration-columnview.vm looks like:
#disable_html_escaping()
#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
$textutils.htmlEncode($duration)
and yes, I restarted the Jira after modifying the file. I am exporting it to excel format..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The above works fine to convert from seconds to hours. How do I convert to show time in 'days'?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.