It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Export Estimates in hours/Day

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

8 answers

1 accepted

1 vote
Answer accepted

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.

I am using 5.0.6

Which version of JIRA you use ?

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.

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

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

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.

Simple and helpful, thank you.   

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.

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

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

*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!

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.

This is not working in Jira 6.1.7 :(

Please help.

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.

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.

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.

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..

The above works fine to convert from seconds to hours. How do I convert to show time in 'days'?

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+.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Jira

Jira Cloud for Google Sheets: Automatically Refresh Your Data!

Remember that time you realized it was possible to refresh your Jira data in Google sheets with just one click? What if we told you that you can now get the latest data with no clicks at all?! Zero! ...

7,366 views 33 29
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you