Export Estimates in hours/Day

Vishnukumar Vasudevan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 14, 2012

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

2 votes
Answer accepted
Vishnukumar Vasudevan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 15, 2012

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.

Marc Addeo August 12, 2012

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.

Marc Addeo August 12, 2012

I am using 5.0.6

Vishnukumar Vasudevan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 12, 2012

Which version of JIRA you use ?

Vishnukumar Vasudevan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 12, 2012

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.

Marc Addeo August 12, 2012

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

2 votes
Andrey Kuzmin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 9, 2015

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
2 votes
Ian Mayoh May 12, 2013

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.

Tanya McPherson July 20, 2016

Simple and helpful, thank you.   

1 vote
Rahul Khire June 3, 2019

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)

Mitali Seal October 24, 2019

I am interested too for v7+.

1 vote
Marc Addeo August 14, 2012

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

Gregory Demotchkine August 12, 2013

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

beatonthematt January 8, 2014

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

beatonthematt January 8, 2014

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!

Marc Addeo January 8, 2014

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.

Tushar Dhumal July 9, 2014

This is not working in Jira 6.1.7 :(

Please help.

Tushar Dhumal July 9, 2014

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.

Marc Addeo July 9, 2014

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.

Marc Addeo July 9, 2014

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.

Tushar Dhumal July 10, 2014

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

1 vote
Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 14, 2012

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.

0 votes
Mark Whyte February 26, 2013

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

0 votes
lladynski July 29, 2012

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.

Vishnukumar Vasudevan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 29, 2012

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.

Suggest an answer

Log in or Sign up to answer