Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

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.

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.

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

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.

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.

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)

Community showcase ### Jira Cloud for Google Sheets: Automatically Refresh Your Data!

Published in **Jira**

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 Connect with like-minded Atlassian users at free events near you!

Find an eventConnect with like-minded Atlassian users at free events near you!

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

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

- FAQ
- Community guidelines
- Privacy policy
- Terms of use
- © 2019 Atlassian

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.